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.

DMCA.com


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

31 Comments

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading