Commenting Best Practices and How to Improve Your Code's Readability

When writing T-SQL, like most code, there’s a good way, a bad way, and a short way to do things.  Sometimes, the short way and the bad way are synonymous.  To assist in being able to write truly ACID (Atomicity, Consistency, Isolation, Durability) Compliant code, one must stick to a few standards in proper commenting and code techniques.  Increasing the readability of your code as well will aid in troubleshooting any bugs that pop up, too.

First things first, commenting your code is important so future users will be able to easily see what the purpose of a store procedure or string originally was without having to read the code itself and try to deduce its purpose.  Always save the string’s name, its author, creation date, and purpose.  Especially in situations when proper naming standards may not be kept up, this becomes incredibly important.  Consider this:

                /* QueryName, Auth. By Ryan Cox on 5.22.2012. This query returns employee names and their sales territory geographical information to be placed in a report titled “Employee Sales Territory by State.” */

                SELECT dbo.DimEmployee.LastName+', '+dbo.DimEmployee.FirstName as EmpName

                                , dbo.DimSalesTerritory.SalesTerritoryCountry

                                , dbo.DimSalesTerritory.SalesTerritoryGroup

                                , dbo.DimSalesTerritory.SalesTerritoryRegion

FROM dbo.DimEmployee

join dbo.DimSalesTerritory on dbo.DimEmployee.SalesTerritoryKey = dbo.DimSalesTerritory.SalesTerritoryKey

ORDER BY EmpName asc

Sure, on a straightforward query like this, you may not need much info on what it does, but as the query gets longer, more information can only help.  You now have a way to know who the author was and what he was trying to do with the code.

Another good technique to aid readability is to capitalize your code as if SQL Server was case sensitive.  It will help the reader’s eyes locate important information or certain snippets when scanning through.  This will also aid in keeping the code consistent with other code that may already be out there.  Also, when you capitalize all of your SQL keywords and functions, they will be easier to distinguish when you view the code in an environment that doesn’t support keyword coloring.  Finally, using singular terms rather than plural will make it easier to remain consistent.  For example, EmployeeTerritory not EmployeeTerritories or employee_territory.

Finally, one should avoid aliasing whenever possible.  Again, for a small query like the one above, it may not post much of a problem.  However, for a longer string of code or several different queries, it gets easy to become confused about whether C.Name was referring to the Customer table or the Categories table.  Also, when you see Customer.Address, it is immediately meaningful without the need to cross reference the join statement.

Using these best practices will aid you in creating code that is meaningful to the user, easy to read and trouble shoot, and consistent to community standards.