SQL Code/Procedure development suggestions

Most of you familiar writing stored procedures in SQL Server, but if you follow the development standards (in my experience)  which would help debugging the code (or) in case code modifications required it is easy to point out. 

Hope the following SQL coding rules help you while writing the SQL procedures & functions.

• Header part of the script should be updated reflecting the current changes to the script with the version details.  Suppose it is a new procedure you could mention as initial draft/release.  Later it can be added with all subsequent changes.

• Alignment of the script content should be proper.  Proper alignment shall help programmer to read the code easily.

• SNAPShot isolation helps reduce the locks, If the DB isolation is Snapshot and the SNAPSHOT isolation is supposed to be used in the scripts then verify that 'SET SNAPSHOT ISOLATION' is mentioned in the script. If in doubt confirm with the team which has sent the script for review and if 'SNAPSHOT ISOLATION' is mentioned, verify that SNAPSHOT ISOLATION is enabled at the database level too

• 'NOLOCK' can be used in the code when the diry reads are allowed which is less lock operation.

• 'SET NOCOUNT ON' statement should be mentioned in the beginning of each procedure. If the scripts require the counts and do not make use of SET NOCOUNT ON, make sure that it uses @@ROWCOUNT server variables instead, for fetching the counts.

• It is good to assign input parameter variables to the local variables then use it in procedure code.

• Fixed length columns in the table should use CHAR datatype all the times.

• TOP Statement might be required/might be present in the scripts. But verify if the TOP statement mentioned in the script is intentional/required.

• Any/All temp tables created in the procedure should be dropped at the end of the procedure

• SELECT statements using temp tables do not require NOLOCK as locks for the temp table maintained internally by SQL Server.

• If any other database references are present in the script, verify that the database is present, always use if exists clause to validate these cases.

• If LINKED Server references are present in the script, verify that the correct LINKED server are referenced and the LINKED Server is present in the appropriate environment.  Better to use If exists clause.

• Instead of multiple SET statements, a single SELECT statement can be used to assign value to variables that avoid confusion.

• Avoid using 'SELECT *' instead, use specific column name(s) in the SELECT statement.

•  Avoid using table variable if data stored in the table is too high.

• Avoid using CTE's if data stored in the corresponding CTE is too high.

• Avoid using co-related queries. See if the same can be resolved using subqueries instead.

• Avoid using CURSOR's if possible, try while instead.  If CURSOR is needed & used, check the CURSOR is CLOSED & DEALLOCATED before end of the procedure.

• Trigger should not be created on any table. If it is absolutely necessary see if an alternative approach can be used to eliminate the trigger.

• Check for Unused variable declarations in the procedures, this can be removed to have only required code.  Dead code / commented code should not be there in the procedure.

• Proper error handling should be done in the code, explaining the error handling in comments would help review the code.  

• Execution Plan of the procedure could help validating the logic and query optimization if needed.

• Validating the performance of the procedure can help figure out the performance bottlenecks.  Execute the procedure using parameters supplied in test environment atleast.