Best Practices in SQL Coding

For any programming language, just writing the code is not well enough. It should be written using the best practices for getting optimise performance . In this article, I will try to explain the disadvantages of writing code without using Best Practices and later how best practices can be implemented in our database code.

Code written without using best practices has the following disadvantages: -

1) Difficult to maintain: - If the code is not written properly, it will be very difficult to modify the code in future and sometimes it become so messy that we required to rewrite the code again even for a small change.

2) Lot of unusable code left which makes the code unnecessary lengthy: – If we do not do the designing part correctly, we keep changing the code again and again which result in lot of reworking. Due to this most of the time it leads to a situation where some stored procedures are left with function or block of query which is not required but it keep executing and decrease their performance.

3) Difficult to understand the code: – One should write a code which can be easily understandable by the other team members. If we do not write the proper comments for each block of code, it becomes difficult to understand the purpose behind the block of code.

4) Poor performance due to improper written complex queries: – In database, there can be multiple ways to write a query which fetch/update/delete records from the tables, but performance depends on how we write the queries. For example, a lengthy complex SQL query containing joins with many tables or SQL query containing distinct command for fetching millions of records where distinct value not required or storing millions of records in a table variable will going to badly affects the performance of the application.

5) Unexpected error or behaviours due to poor error & transaction handling: - If exception handling is not done properly, query execution may break in midway and wrong data is inserted into the tables which will corrupt the database.

6) Locking of tables for long duration due to poor transaction handling: – Transactions should be used so that in case of any exception or error, uncommitted data must be rolled back. But sometimes we use transaction so poorly that it locked the main tables for longer time, and it resulted in the deadlock situation and long duration of locking period result in poor application performance.

7) Difficult to debug: – If the code is written in a very messy way with lot of unusable codes, it becomes difficult to debug the code and find the error.

Best practices for writing SQL code

Easy to understand

• Every code of block should have proper comments defining the purpose of this block of code.
• Use indentation and line breaks for readability.
• Table alias should be meaningful.
• Up-to-date documentation of database schema, relationships, and data flow should be maintained.

Stored procedure and functions

• Stored procedures should be preferred over ad-hoc queries for better security, better performance & code re-usability
• Stored procedure with complex logic should be break down into smaller stored procedures and functions.
• Code should be reused by creating utility procedures and functions.
• Dynamic SQL query should be used only when necessary
• Parameterized queries should be used to prevent SQL injection.
• Returning unnecessary rows or columns should be avoided to minimize data transfer and improve performance.
• Instead of embedding values directly in your SQL, parameters should be used to avoid SQL injection and improve execution plan reuse.

Optimize Joins

• Try to use SQL Joins instead of Sub queries where possible for better performance.
• Use the appropriate type of join (INNER, LEFT, RIGHT, FULL) based on the requirements.
• Ensure joined columns are indexed.

SQL Indexes

• Create appropriate indexes on columns frequently used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
• Use Covering Index to include all the columns of the select query in the indexes.
• Be careful with indexes on columns that undergo frequent updates, as they can slow down DML operations.
• Clustered index should be avoided on the table which get very frequent DML changes due to high cost of maintaining the clustered index which affect the performance very bad.
• Creating too many indexes can also decreased the performance, as it takes cost in maintaining the indexes.

Avoid Functions on where clause columns

• Using a function on the where clause column prevents SQL server to use index on that column which result in poor performance

Transaction Handling

• Transactions should be handled properly in the stored procedure so that in case of error, uncommitted data can be easily rollback.
• Transaction duration should be short so that it should not keep the tables locked for longer duration which affects the application performance negatively Instead of applying transaction in the whole stored procedures, multiple transactions can be applied in stored procedures by keeping the related block of code in a single transaction.
• User interactions should be avoided within transaction.
• Isolation level should be set based on the balance between consistency and performance.

Error Handling

• Proper error handling using Try….catch should be done to manage log and errors. Catch block should give proper error message.
• In case of any errors, rollback transactions should get executed within the CATCH block to save tables data from getting corrupted due to uncommitted data. It helps in maintaining the database integrity.

Fetching data optimisation

• Instead of “Select * from tablename” command which select all the columns of the table, try to fetch records of only those columns which are required as it improve the query performance.
• Distinct command should be used only when we need the unique results as Distinct command adds additional cost to the query.
• If we do not need the unique results, use Union all instead of Union.
• If we just need to verify the existence of a value in a table, prefer EXISTS to IN, as the EXISTS process exits as soon as it finds the search value, whereas IN will scan the entire table.

Avoid Nested Loops

• Try to minimize the use of nested loops and subqueries where possible.
• Consider using common table expressions (CTEs) or derived tables for complex queries.

Avoid Cursors

• Use set-based operations instead of row-based operations.
• If cursors are required, use FAST_FORWARD or READ_ONLY cursors.

Keys and constraints

• Relationship between the tables should be defined properly using the Primary & Foreign keys where possible as it helps in maintaining the database integrity and better database performance
• Use appropriate constraints such as NOT NULL and UNIQUE to ensure data integrity.

Temporary table vs Table variable

• Try to use table variable when the number of rows which are going to store in the table is small & temporary table should be used if the number of records which is going to be stored in the table is large.

Appropriate use of datatype

• Choose the smallest appropriate data type for each column.
• Instead of using generic data types such as VARCHAR for all data types regardless of data size, datatype & its data size should be defined properly based on the data we are going to stored.

Use of "set not count on"

• Unless there is a need to know the number of rows affected by the execution of the query/stored procedure, we should use “set not count on” at the beginning of the code block and “set not count off” at the end of the code block as calculation of the number of rows affected add extra cost to the performance.

Monitor and Optimize Regularly

• Use SQL Server Profiler and Execution Plans to monitor and optimize queries regularly.
• Creating Indexes is not enough. We need to create indexes maintenance plan to check index fragmentation and accordingly reorganized or rebuild the indexes.
• Regularly update table statistics.

Summary

This article tried to show the disadvantages of not following the best practices and put lights on some best practices which we can use during SQL coding to make our database code more flexible, reusable, easy to understand and more optimised.

For more arcticles on SQL Server, you can visit Technology with Vivek Johari

Would love to hear in the comments ??.

Repost this & follow me Vivek Johari for more tips and articles.

#SQLOptimization #SQLPerformance #DatabaseTuning #QueryOptimization #SQLBestPractices #SQLTuning #DatabasePerformance #SQLTips #TechTips