T-SQL Code Readability and Format

This standard should provide guidance for developers who are writing and working in T-SQL. This for all T-SQL code including stored procedures, function, etc…

Philosophy

We want to have code the is readable and formatted in a consistent manner across enterprise. This will help provide high quality development as well as it will lower the cost of future maintenance of the T-SQL code.

Comments

The goal here is to help understanding by adding comments to the code. Remember that when we write code we want it to be self-documenting. This means that it should be written in a clean standard way that by reading the code it is clear what the code is doing. If this is not true for a section of code that was written you should consider refactoring it in to a way that is.

When this is not possible you should include a comment to aid in understanding what is happening in the code. You should also include comments when you do something that is nonstandard.  We want to make sure that any break from the normal is documented and understood both in the future and at code review time.

Do not worry about length of comments it does not affect performance.

Formatting T-SQL

We want to make sure that all T-SQL code that is written in the enterprise should follow a standard formatting guide. This provides the ability for the team to easily read and maintain code that they did not write. The format details do not matter as much as the fact that everyone in the enterprise is doing it the same. I recommend using a tool to handle the formatting it makes it easy to use and maintain.

Recommended Tool:

Redgate SQL Prompt

https://www.red-gate.com/products/sql-development/sql-prompt/

How to use

Run Format

Or

press Ctrl + K + Y (hold down Ctrl and press K then Y)

Before Format:

After Format:

If you want more information on how Red Gate SQL prompt formats code take a look at:

http://documentation.red-gate.com/display/SP7/Using+styles+to+format+your+code

Standard Header

The header being a part of the T-SQL code allows anyone modifying it or reviewing it to immediately be able to see who the original creator is, the creation date, the original purpose of the object and the subsequent modifications that have been made to it in production.

This header should be applied to all T-SQL scripted objects.

Template Example:

1. Unit – This is the name of the object that the header is for.
2. Created By – This is the name of the user who created this object
3. Create Dt – Is the date in which this object was created.
4. Work Item– This tracking number that you use to go back to review why this was created.(Ticketing system, TFS, ETC…)
5. Purpose – This is an overview of the purpose of the object. It would be good to include key requirements from work item.
6. Reads – This is any objects that this object reads from.
7. Writes – This is any objects that this object writes too.
8. Executes – This is anything object that this SP executes.

Piano horizontally. flickr photo by dslrninja shared under a Creative Commons (BY) license