For this month's T-SQL Tuesday, Deb Melkin asks us to get up on our favorite soapbox:
My biggest challenge for this topic is which soapbox do I choose? The easy way out is to regurgitate something I've written about before. Last month, I talked about CONVERT vs. CAST which, surprising even to me, was a rant I hadn't spewed before. Continuing that trend, let's talk about…
I was asked to review some code recently, and it looked like this:
SELECT CustomerID , FirstName + N' ' + LastName AS FullName , Address1 , Address2 FROM Customers WHERE CustomerID IN ( 12345 , 6789 , 54321 ) ...
What fresh hell is this?
I find they heavily interfere with my ability to read the code – it's extra noise that I have to force myself to ignore and skip past, like tables with the
tbl prefix. And much like with
CAST, I often wonder, "what is the motivation here? How is this possibly better?"
And I've had the conversation with folks who prefer it this way. The answer is almost universally:
During debugging, it makes it easier to comment out a line."
Which line, though? Not the first one! Let's look again:
SELECT CustomerID -- if you comment this, error! , FirstName + N' ' + LastName AS FullName , Address1 , Address2 FROM Customers WHERE CustomerID IN ( 12345 -- if you comment this, error! , 6789 , 54321 ) ...
If we compare the above to my way, which line is hard to comment now?
SELECT CustomerID, FullName = FirstName + N' ' + LastName, Address1, Address2 -- if you comment this, error! FROM Customers WHERE CustomerID IN ( 12345, 6789, 54321 -- if you comment this, error! ) ...
Slightly more sane, IMHO
Only the last one. But every single line is easier to read because I don't have to mentally ignore the leading
Not that I think code should permanently favor debugging over readability, but when I'm troubleshooting, I typically want to put the column(s) I'm messing with at the beginning of the result set anyway, since then it appears on the left in the results. If I'm messing with a query by putting those column(s) last, then they appear all the way to the right in the results. Who likes horizontal scrolling, especially during debugging?
Anyway, as Brent Ozar tells us, you should Never, Ever, Ever Start T-SQL Comments with Two Dashes. However, if it's really your jam, and you don't mind messing up monitoring tools and other diagnostics, I recommend this combined format from Andy Mallon instead, where the trailing multi-line comment marker is prefixed with a single-line comment:
/* alias = expression, (this is a multi-line comment) --*/
It's a few extra characters of work to set up, but look how easy it is to make it no longer a comment (you just make the opening comment marker a single-line comment):
--/* alias = expression, (this is no longer a comment) --*/
Another justification I've heard:
Commas at the end make me more error-prone."
I raised an eyebrow the first time I heard this one; while I understand the scenario, it is certainly exacerbated by other less-optimal practices (like not using
AS with trailing aliases):
SELECT col1 colA, col2, col3 col4 FROM ...
Spot the missing comma
The outcome of this statement will put the values of
col3 into another output column called
col3 won't actually be a part of the result set. If you're debugging and actually half awake, this should not cause a problem for very long.
So please stop using "I need to use single-line comments in this very specific way in the occasional troubleshooting scenario" or "I'm really bad at spotting missing commas no matter where I put them" as excuses for making all of your code less readable all the time.