Best practices for Database Programming

1. All the tables in the database have properly defined relationship using primary keys and foreign keys .2. Indexes should be created on the tables as it increases the performance of the SQL Queries.

3. Find the indexes which need to be reorganized or rebuild.

4. Use “set not count on/off” as it will increases the performance of the queries.

5. Try to avoid the cursor and replaces it with while loop or using switch statements or Sub Queries.
since until the cursor is not deallocated, memory is occupied by the cursor data and in case of large amount of data, it can decreased the performance of the sql queries.

6. Proper use of temporary tables and table variable.

7. Use Joins instead of Subqueries.

8. Intelligent use of Distinct command. Distinct command add additional cost to the query. So we should use Distinct commands only when we needs the unique results.

9. Proper error handling using Try….catch.

10. Do not use “Select * from tablename” command when you only needs to select few columns from the table.

11. Keep the Transactions as short as possible

12. Never use “sp_” prefix in your store procedure name.

13. Use SQL Profiler to monitor the SQL performance.

14. Use stored procedure instead of inline Queries in the code.

15. If we do not need the unique results, use Union all instead of Union.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Best Practices, SQL Server and tagged . Bookmark the permalink.

31 Responses to Best practices for Database Programming

  1. Anonymous says:

    Nr. 5 (Try to avoid the cursor and replaces it with while loop…) is like recommending fighting fire with flooding… A WHILE loop still process one row et a time. Avoid the cursor is correct. But avoid a WHILE loop, too! Use a set based solution whenever possible and avoid any form of row-based data handling.

  2. loquin says:

    Regarding Number 2 (Indexes should be created on the tables as it increases the performance of the SQL Queries.)

    This is true. However, they also slow down inserts, updates, and deletes, and increase storage requirements. On active tables, with many concurrent writes and reads, do not add indexes without considering the impact on table updates.Instead, add indexes ONLY where they are needed, else, it could make your system slower overall.

  3. Pingback: cpasbien

  4. Pingback:

  5. Pingback: bong88

  6. Pingback:

  7. Pingback: paper crafting dies

  8. Pingback:

  9. Pingback: New African Generation

  10. Pingback: w88ok

  11. Pingback: xembongtructuyen

  12. Pingback: cbd oil tastes like

  13. Pingback: Dream Market Deutsch

  14. Pingback: Nachfolger

  15. Pingback: Drogen

  16. Pingback: m88vina

  17. Pingback: website

  18. Pingback: Manual Resuscitator Market: How Top Leading Companies Can Make This Smart Strategy Work 2016 – 2026

  19. Pingback: Digital Income System

  20. Pingback: m.w88

  21. Pingback: web design dallas tx

  22. Pingback: ratucapsa

  23. Pingback: senangpoker

  24. Pingback: bitcoin qr code generator

  25. Pingback: daftar qiuqiu99

  26. Pingback: poker99

  27. Pingback: trang m88

  28. Pingback: brokerbin

  29. Pingback: Skrota bil

  30. Pingback: pasar poker

  31. Pingback: duratrans printing nyc

Leave a Reply