SQL Server Coding Standards: T-SQL Query and Batch Practices

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Always try to write queries that follow the latest ANSI-SQL standards. Try to avoid using features which are non-standard as they may tend to be depreciated in future versions of the product.

Always write all T-SQL Keywords like CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, TRUNCATE, SELECT, FROM, WHERE, JOIN, INNER, OUTER, FULL, ON, WITH, GROUP BY, ORDER BY, HAVING, AS, CASE, IF, WHILE, BEGIN, END and all the other T-SQL keywords in UPPER CASE only.
This makes queries more readable and user-defined objects referenced in the queries can be identified quickly from the SQL Keywords. Following is a simple example of a query with all its T-SQL Keywords in UPPER CASE.

1 2 3 4 5 6 7 8

SELECT FirstName ,LastName ,BirthDate ,EmailAddress FROM [dbo].[tbl_Customers] WHERE LastName LIKE 'J%' ORDER BY LastName,FirstName

Always reference the objects in queries by two-part referencing convention like [SchemaName].[ObjectName] for appropriate object name resolution. Try to avoid following types of object reference in queries:

1 2 3 4 5 6 7

SELECT FirstName ,LastName ,BirthDate ,EmailAddress FROM tbl_Customers WHERE EmailAddress IS NULL

Instead reference the object by explicitly specifying the Schema Name:

1 2 3 4 5 6 7

SELECT FirstName ,LastName ,BirthDate ,EmailAddress FROM [dbo].[tbl_Customers] WHERE EmailAddress IS NULL

This way, it is guaranteed that always the correct intended object will be referenced if there are
two objects with the same name in two different schemas.

Always, write clear and useful comments for complex SQL Statements which are not obvious and require explanation. This helps developers to understand the code easily written by someone else.

Use comments in Stored Procedures, Views, User Defined Functions, Triggers and anything which is complex and needs explanation. Explain what a particular statement is doing and how by placing comments above the statements.

Use single line comments with double hyphen (–) characters and multiline comments spanning across multiple lines using /* */ commenting blocks.

At the beginning of an object definition, use multiline comments to specify the name of the object, purpose of the object, name of the author.

Additionally, any revision or change in the object definition should be specified with the Change Date, the name of the person who makes a change and description of the change. Some examples of the same can be seen in Sample Code Snippet sections.

Use of GOTO statements should be avoided.

Statement blocks within BEGIN END, BEGIN TRANSACTION…COMMIT TRANSACTION, BEGIN TRY…END TRY and BEGIN CATCH…END CATCH should be indented one tab right side.

IF and ELSE block is more readable when using in following format:

1 2 3 4 5 6 7

IF SomeCondition BEGIN --IF Statement Block END ELSE BEGIN --ELSE Statement Block END

Prefer using static queries and avoid dynamic SQL queries in T-SQL batches as much as possible.

When it is mandatory to use dynamic query, do not execute a dynamic query with EXECUTE statement. Rather use sp_executesql stored procedure and make it parameterized dynamic query by passing the appropriate parameters to sp_executesql stored procedure.

Follow the set-based theory and do not use cursors until there is not any alternative solution to using cursors. Generally most of the tasks can be achieved with set-based theory by using complex DML Statements and combination of temporary tables.

Use Cursors only as a last resort as cursors are very resource intensive and poor in performance.

Even if there is a compelling reason of using cursor, consider if same functionality can be achieved with the WHILE loop. Try both, WHILE loop approach and CURSOR approach, compare them and use the one which is efficient.

If cursor has to be used then consider to make it read-only, forward-only cursor.