When you write TSQL code what kind of coding style do you use? Do you have a consistent way to comment and format your code? Does your organization provide standards for how you must code your TSQL? Even if you replied NO to all these questions you probably have a style you use when you write your TSQL code. In this article I will talk about some of the coding styles you should consider when writing your TSQL scripts and stored procedures.
What is a Coding Style and why is Having a Coding Style Important?
A simple definition of coding style is the way you write, punctuate and format your TSQL code. Having a coding style allows you to easily read and maintain your TSQL code.
You may think you don’t have a TSQL coding style. But you probably do have a style, unless you are just getting started coding TSQL code. After you have coded a few different SELECT, INSERT, UPDATE, or DELETE statements you more than likely start to develop a particular coding style for how you put those statements together.
I’m not here to say one style is better than another. But instead this article is meant to expose you to different styles of writing TSQL code to improve the readability of your code. It will be up to you and your organization to determine what coding style works best in your environment.
In order to put some framework around what I mean when I say “Coding Style” I provide the following list of items that I consider part of a coding style:
- Documenting code
- Formatting code
- Naming guideline
This list is by no means complete. I’m sure there are additional coding style items that you can think of that should be added to this short list. Further down in the article I will be discussing these different coding styles in more detail.
About now you are probably wondering how a coding style makes you a better TSQL programmer. By having a consistent TSQL coding style you will find it easier for you to read, debug and maintain your code. Also it will help other people read and understand your code. If you develop some consistent coding styles then all scripts and TSQL will be much easier to read and maintain.
Documenting Code
I think we have all heard from early on in our careers that documenting code is important. I’m sure you have gone back to some of your old code that contained little or no comments and wondered what the heck your code does. If as you write your code you should document it, then it will be much easier later on to understand what your code does.
When I talk about documentation I’m not talking about writing a word document about what your code does and how it can be used. Instead I’m talking about the inline comments you put in your code. These are the comments that explain and provide you with clues to help you understand what the different chunks of code do in your TSQL script or stored procedures. Keep in mind any comments you provide in your code is not only to help you, but to also help other programmers to understand what your code does long after you have moved on.
As for how and what should be documented it is up to you. You might want to put a header on each script explaining what it does, when it was written and who wrote it. This helps provide a quick reference to what your code does, how long the code has been around and who was the author of a script or stored procedure. If you took the time to write the code why wouldn’t you want to take the time to give yourself credit for writing the code by placing your name in the code?
In TSQL there are two different styles for providing inline comments in your code. You can either create a comment code block using the /*…*/ notation, or you can place a — (dash dash) in front of your comment. You might think about how you use these two different notations to provide different documentation and allow you to simplify how you use your code.
I like to use the /*…*/ notation for documenting what my code does and the history of the modifications that I’ve done to my code over time. I do this with a comment block right at the top of my code. By doing this it makes those comments in my code easier to type and read.
I additionally use the /*…*/ notation to place code snippets within my scripts that I don’t necessarily want to run all the time as part of my script. I might what to use the code snippets to launch my code, use it to recover from a failed run, debug my code, etc. For example when I’m writing a stored procedure that accepts multiple optional parameters, I might have a few different formats of EXECUTE statements where each one has a different set of parameters that I can use for testing my store procedure, something like this:
/* Here are some examples of how you might call MyProc. EXEC MyProc; EXEC MyProc @Parm1=99; EXEC MyProc @Parm1=99. @Parm2='True'; */
By doing this when I want to test my stored procedure after I CREATE or ALTER my stored procedure I can quickly test out different parameter combinations, by just highlight the appropriate EXECUTE statement that I want to execute.
Another use of “/*…*/” notation is to comment out a large blocks of code as well.
As you write your code you should sprinkle in one liner, or partial line comments to explain what your code does. Using the “–“ (dash, dash) method is good to provide inline comments. It is best to add these comments as you are writing your code, instead of trying to add them after you have completed your coding. I find if I write comments as I go along my code is much better documented. It is best to have too many comments then not enough.
Formatting Code
To help with the reading of code, and understanding the flow of the code you need to adopt a coding format. By format I mean how you use uppercase/lowercase, indentation, alignment of code and carriage return. Depending on how you handled each one of these aspects could determine how readable your code might be.
First let me discuss using upper and lower case for keywords. By keywords, I mean words like SELECT, FROM, WHERE AND, OR, etc. To help show how case can make your code more readable let me start with this simple SELECT statement where all keywords are in lower case:
select id, myname, mydesc from dbo.mytable where id > 20 and proddesc like '%boat%';
Here both the keywords, columns, schema name, name and WHERE condition are all in lowercase. In this second SELECT statement all the keywords are in uppercase:
SELECT id, myname, mydesc FROM dbo.mytable WHERE id > 20 AND mydesc LIKE '%boat%';
You decide which SELECT statement is easier to read.
We have all seen those TSQL statements that wrap onto multiple lines in the query editor within SQL Server Management Studio. To eliminate the long run on TSQL statements you need to break up your statement by inserting carriage returns where it makes sense within the statement.
I feel there are at least three parts to the SELECT statement. There is the column list, the FROM clause, and the WHERE clause. If you break each of these up onto multiple lines it can help eliminate those long TSQL statements. Below is one way I could break up my sample SELECT statements:
SELECT id, myname, mydesc FROM dbo.mytable WHERE id > 20 AND mydesc LIKE '%boat%';
My query has a very short list of column names, additionally the FROM and WHERE clause are not that lengthy as well. But if you had a statement that had a long list of columns, and the WHERE clause had many conditions, that might cause all of these sections of your TSQL code to wrap around onto multiple lines in the query editor. If this happens then you could include additional carriage returns between each column and WHERE condition like so:
SELECT id , myname , mydesc FROM dbo.mytable WHERE id > 20 AND mydesc LIKE '%boat%';
Separating the different columns and the WHERE conditions onto separate lines also makes it easy to comment out columns or WHERE conditions while you are debugging your code.
If you really want to be consistent in how you format your code you might want to consider using a formatting tool. There are a number of free tools out there. To find one just enter the string “sql formatting tool” into your favorite search tool.
Naming Guidelines
When you write a TSQL statement it will consist of one or more object names that reside in your database. Since a database has multiple schemas you should consider qualifying all of the objects you reference. This will make it easier to read and distinguish exactly which object is being reference. In my code above you can see that I placed the prefix “dbo” in front of my table name.
Another naming consideration is what alias names you use for table or column names in your code. When defining your alias names you should try to pick meaningful names. Consider the following code:
SELECT B.AddressID AS A ,A.BusinessEntityID AS B ,C.ContactTypeID AS C FROM [Person].[BusinessEntity] AS A JOIN [Person].[BusinessEntityAddress] AS B ON A.BusinessEntityID = B.BusinessEntityID JOIN [Person].[BusinessEntityContact] AS C ON A.BusinessEntityID = C.BusinessEntityID;
In this code I used meaningless table and column alias names. By doing this it doesn’t provide names that help the reader determine which tables or column names are associated with the alias names. Now consider this code, where I have taken the code above and used slightly more meaningful alias names:
SELECT BusEAddr.AddressID AS AddrID ,BusE.BusinessEntityID AS BusEID ,BusECon.ContactTypeID AS ConTypID FROM [Person].[BusinessEntity] AS BusE JOIN [Person].[BusinessEntityAddress] AS BusEAddr ON BusE.BusinessEntityID = BusEAddr.BusinessEntityID JOIN [Person].[BusinessEntityContact] AS BusECon ON BusE.BusinessEntityID = BusECon.BusinessEntityID;
Here I abbreviated the names to make up the table and column alias names. I think this code is much easier to read and understand which column and table names are being referred by each alias name.
Table and column aliases are not the only object names that we tend to establish when we write code. We also define local and global variables in our code. When defining these variables you should also establish meaningful names. Here is an example where I used a short meaningless variable name:
DECLARE @A varchar(30); SELECT TOP 1 @A=Name FROM Production.Product SELECT @A
In this code I used the variable @A to hold the value of the Name column from the Production.Product table. @A is one of those meaningless variable names. Consider declaring your variable names with a name that describes the value they will represent. Since @A really contains the name of a Product changing the name of this variable to @ProductName, like I did below, makes my code more readable:
DECLARE @ProductName varchar(30); SELECT TOP 1 @ProductName=Name FROM Production.Product SELECT @ProductName
Avoiding Lazy Practices
When you are writing any piece of code, use a consistent coding style. Avoid those lazy practices that show you don’t care about formatting, punctuation and use of naming standards. Any piece of code worth writing is worth writing well and documenting appropriately. You need to determine what kind of coding style works best for you and your environment. Once you determine your coding style use your style to format every piece of code you write, no matter how small or large the code snippet might be.
