Download
7 things i wish developers knew about sql server n.
Skip this Video
Loading SlideShow in 5 Seconds..
7 things "I" wish Developers Knew about SQL Server PowerPoint Presentation
Download Presentation
7 things "I" wish Developers Knew about SQL Server

7 things "I" wish Developers Knew about SQL Server

112 Vues Download Presentation
Télécharger la présentation

7 things "I" wish Developers Knew about SQL Server

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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!