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 explain the disadvantages of writing code without using Best Practices in SQL Coding. Later, I will describe 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. We must write proper comments for each block of code. Without them, it becomes difficult to understand the purpose behind the block.

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.
• Do not embed values directly in your SQL. Use parameters to avoid SQL injection. This practice also improves 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.
• Avoid using a clustered index on tables with very frequent DML changes. Maintaining a clustered index incurs a high cost, which negatively affects performance.
• 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. This ensures that in case of error, uncommitted data can be easily rolled back.
• Transaction duration should be short. Long locks on tables affect application performance negatively. Instead of applying a transaction to the whole stored procedure, use multiple transactions. Keep related blocks 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, execute rollback transactions within the CATCH block. This prevents tables data from getting corrupted due to uncommitted data. It helps in maintaining the database integrity.

Fetching data optimisation

• Do not use the “Select * from tablename” command. This command selects all the columns of the table. Instead, fetch only the required columns. This approach improves the query performance.
• Use the Distinct command only when unique results are needed. It 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. The EXISTS process exits as soon as it finds the search value. In contrast, 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 a table variable when the number of rows to store is small. Use a temporary table if the number of records to store is large.

Appropriate use of datatype

• Choose the smallest appropriate data type for each column.
• Do not use generic data types such as VARCHAR for all data types regardless of data size. Define the datatype and its size properly based on the data we are going to store.

Use of “set not count on”

• We should use “set not count on” at the beginning of the code block. Then use “set not count off” at the end of the code block. This is necessary unless you need to know how many rows the query or stored procedure affects. Calculating the number of rows affected adds 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. It highlighted some best practices for use during SQL coding. These practices can 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

The post Best Practices in SQL Coding appeared first on Technology with Vivek Johari.