It’s that time of the month again for TSQL2sday, this time around, hosted by Amit Banerjee (Blog|Twitter). The topic for the twentieth edition: T-SQL Tuesday #20 – T-SQL Best Practices. Follow the link to see all of the other contributions made by the wonderful Online SQL Server Community, here is my humble contribution. And many thanks to Adam Machanic (Blog|Twitter) for hosting the very first TSQL2sday event!
In my experiences and in my opinion as a SQL Server Developer, one of the best ways to help manage and mitigate SQL issues is to at least encourage consistent formatting within T-SQL code. In one of IT shops I used to work for, each project was handled by a different IT resource, whether it was a developer or a DBA or even a Business Analyst who knew how to write T-SQL. T-SQL would be written, would be deemed to work, and then implemented without question or with little to no peer review. The process worked and that’s what matters most, right?
Cartoon Image from: http://c2.com/cgi/wiki?SpaghettiCode
Well, when I became a follow up IT resource tasked with Production Support on these processes, it amazed me how much the T-SQL itself was written with no formatting whatsoever. For the most part, the term ‘spaghetti code’ came to mind when I witnessed T-SQL all garbled up with inconsistent or no formatting. Ironically, the code would work, but something new was introduced—whether it was new data or new business rules—that now required the T-SQL to be fixed. And my, did it need fixing. I recommended using free online tools such as TSQLTidy.com (Blog|Twitter) to quickly clean up any T-SQL that came across that was deemed unreadable. While I praised TSQLTidy.com to members of my team, only a few took note, and unfortunately for some, promoting better T-SQL formatting became a lost cause.
Spaghetti Code Image, as shown from WikiPedia
So while I encourage consistent formatting in T-SQL code, here is an example of how I would apply good formatting practices that I observed from two different shops. The first image is just a sample query used against the AdventureWorks database:
Within the script above, there is a lot of inconsistent formatting and code use. Mind you, the query correctly pulls the desired results, but I would hate to support Production code where the majority of the T-SQL looked like this. (Actually, I did hate supporting this.) The point is, if a little effort was done to clean up the script, maintenance and troubleshooting would be much easier for the next IT resource to manage.
So in one shop, I would reformat the query as such:
To clarify what I did, here are some notes with the following image:
- Use of the TAB value when indenting groups of lines.
- Use of braces to identify all returned columns from the query, where the name in braces is followed by the equal sign and the value for the returned column.
- Always aliasing all tables, and all fields appropriately prefixed with the alias. This is done so that every referenced field can be traced back to which table it is sourced from.
- Use of the braced returned columns within ORDER BY clauses.
In another shop, I would reformat the code as such:
And again, to clarify what I did, here are some notes with the following image:
- Instead of returned columns noted by braces, the AS syntax is used consistently with each returned column in a quoted name.
- Use of a ‘river of white space’ over tabs in order to format the query around a column of spaces.
- Still, always aliasing all tables, and all fields appropriately prefixed with the alias. This is done so that every referenced field can be traced back to which table it is sourced from.
- Use of the quoted returned columns within ORDER BY clauses.
This is by no means a how-to document on the best way to format T-SQL. If anything, the message is to be consistent with whatever formatting style used by the company. Check with senior team members and even the DBA team for their recommendation, and follow suit, if only to have a common look for all of the Production T-SQL code. Sure, it may be a daunting task to re-format everything, but if anything has to be maintained or managed or enhanced, then theirs is your opportunity to clean up the inconsistent code.
As s quick note, here is what TSQLTidy.com will do for us:
So in short, a T-SQL Best Practice: Consistent formatting of T-SQL code. It’ll save some stress, and it’s not only a best practice, it’s a good practice!
Thanks for reading my TSQL2sday entry!
And as always, just my two and a half cents! Thanks!