7 things "I" wish Developers Knew about SQL Server

1. 7 things "I" wish Developers Knew about SQL Server Vinod Kumar M Technology Evangelist | Microsoft http://blogs.sqlxml.org/vinodkumar www.ExtremeExperts.com

2. Agenda These are my WISH LISTS • Common “7” Mistakes • My “7” Wish list for developers • Demos, demos and more demos …

3. Common "7" Mistakes first !!! • Not highlighting WHERE clause in DELETE • In RDC – Shutting down instead of Logoff • Checking – “Drop Dependent Objects” • Hitting F5 instead of F6(parse only) • GUI VS TSQL battle – know it, then use it • Taking backup’s – Do we need one? • Running scripts without Transactions • List can go on ….

4. 1. Must Knows !!! • You don’t have excuses for this

5. Must Knows!!! • SQL Server Books Online • Updated regularly • SQL Server Management Studio • Source Control • Query Plans • Data Browsing • Profiler • Monitor SQL Server activity on the server

6. DEMO Knowing the Tool - SSMS

7. 2. Know your Identity 

8. Identity Problem • Identity Keys • Necessary evil • Capturing the IDENTITY column values • Olden days used @@IDENTITY • Know the new constructs to work efficiently • Know the options here and the reason

9. DEMO Understanding the IDENTITY Crisis

10. 3. The "X" factor 

11. "NULL" factor • NULL’s are unknown values • ANSI-92 mandates: • NULL = NULL returns false • NULL <> NULL returns false • Handling NULL challenges • Use WHERE ColName IS NULL • AVOID: WHERE ColName = NULL • AVOID: Using OR predicate or COALESCE • Know your data !!!

12. DEMO Know NULL behaviours and needs

13. 4. Conversions 

14. Conversions • Happens with DateTime often • Can become performance bottlenecks • Worst are Implicit Conversions • You own your database schema and hence own these pitfalls too • Not always are these conversions bad  … • Know them

15. DEMO Conversions of any sort have overheads

16. 5. Fatal Exception – Errors 

17. Error Handling • Tough with SQL Server 2000 • Yet manageable • Had to understand the scope to handle errors • New handles with SQL Server 2005 • Be innovative on handling errors • Errors are mostly logical in nature • Can nest TRY-CATCH

18. DEMO Handle Errors properly

19. 6. Know your transactions

20. Transactions • COMMIT TRAN matches BEGIN TRAN • ROLLBACK TRAN cancels ALL transactions • Always BEGIN, COMMIT and ROLLBACK transactions at the same level • Adhere to some coding practices • Always test @@TRANCOUNT before COMMIT or ROLLBACK • Know these transactional pitfalls in your code

21. DEMO Transactional Behaviours

22. 7. Improper Normalization

23. Normalizations – Be practical • AFAIK, all developers must be part of Database design phase • Normalized based on: • Requirement or Data distribution • Application access mechanism • Pitfalls • No or poor indexing • No or poor Key management • Missed opportunities to de-normalize

24. DEMO Don’t Normalize to death …

25. Summary • These are not tricks, these are things to remember ALWAYS. • Most of them are easily implementable: • Identity Problem • Error Handling • Conversions etc • Know your tool inside-out

26. આભાર ধন্যবাদ நன்றி धन्यवाद ಧನ್ಯವಾದಗಳು ధన్యవాదాలు ଧନ୍ୟବାଦ നിങ്ങള്‍‌ക്ക് നന്ദി ਧੰਨਵਾਦ

27. Contact • Blog Address http://blogs.sqlxml.org/vinodkumar • Webspace www.ExtremeExperts.com

28. Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form at: << Feedback URL – Ask your organizer for this in advance>> For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!