If you have ever searched for “commenting in TSQL” scripts, I am sure that you’ve found hundreds or maybe even thousands of blogs or articles written about them. This blog won’t be much different from others out there and it is a very, very, very basic concept. However, time and time again, I come across stored procedures or scripts that aren’t very well documented. This was especially in past environments that I have worked and this is not meant to be negative in nature, but documenting your scripts, will definitely help others (and you) understand your thought processes and what you are trying to accomplish at that time.

It is so difficult to try and follow a complex script that is hundreds (or even thousands) of lines of TSQL. Going through scripts that you’ve created months or years before without any documentation is a tough thing to do, but it will save you time in the long run. Because there will come a time when your script or procedure will be modified or completely redesigned. Nobody really enjoys documenting, but let’s try to alter our perspectives a little bit. Maybe we should take pride in each and every one of our scripts and give it that little finishing touch to make it more manageable in the future. Show those who follow behind you what real documentation is like! Alright… enough rambling right?

NOTE: Commenting does not affect performance of the script or procedure. That is unless you forget to open/close a multiline comment appropriately! J

Just for those that are newer to TSQL or those who have forgotten how to comment, below are a few examples of how we create single line commenting, parts of a line and multiline.

Single Line Commenting

Commenting an entire single line is performed by putting two hyphens (-) in front of your line

Multi Line Commenting

Commenting multiple lines at the same time could be done using the double hyphens (-), but you can save yourself some typing if you just open it with forward slash and asterisk (/*) and close it with the opposite pattern; asterisk and then forward slash (*/).

Script Example

--COMMENT ENTIRE SINGLE LINE: Get First employee based on Name

SELECT

            TOP 1 Name—COMMENT PARTIAL LINE, Employee_AK

FROM

            DimEmployee

ORDER BY

            Name

/* MULTILINE COMMENT

See how you can have line after

line of commenting to inform others

what you are trying to do in a script?

*/

At any rate, this post isn’t as much on how to comment in TSQL, it is meant as more of a kick in the butt in saying that commenting should be done! If at the very least, so when you look at the procedure that you created last year for project xyz, you can at least somewhat remember what the heck you were thinking when you wrote the script.  

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter