1 / 29

7 things "I" wish Developers Knew about SQL Server

7 things "I" wish Developers Knew about SQL Server. Vinod Kumar M Technology Evangelist | Microsoft http://blogs.sqlxml.org/vinodkumar www.ExtremeExperts.com. Agenda. These are my WISH LISTS Common “7” Mistakes My “7” Wish list for developers Demos, demos and more demos ….

haruko
Download Presentation

7 things "I" wish Developers Knew about SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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!

More Related