T-SQL Tuesday #152 : Soapboxing

T-SQL Tuesday #152 : SoapboxingFor 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…

Leading commas

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?

Lots of readability issues there. We could talk about using AS instead of = for the expression, or the lack of schema, or starting a paren at the end of a line, but let's focus on the leading commas.

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 col4, and 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.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him.

8 Responses

  1. Simon says:

    Interesting that you quoted Brent Ozar. He also says (which I strongly disagree with) that DBAs shouldn't complain about the way devs format their SQL code, and recommends DBAs reformat the code themselves.

    • I don't agree with everything Brent says either (sorry Brent), but that doesn't mean that by extension I have to disagree with everything he says, either. The quote about using a leading -- comment is not simply an opinion, it's based on a very real and tangible impact to monitoring systems and other diagnostics.

      • Simon says:

        Sorry, I didn't mean to suggest that you should, I just thought it was ironic, that's all.

  2. Shane says:

    100% leading commas, I view them as bullet points for my list of columns. I can quickly see if one is missing as it will stand out or if it's a multi-line column e.g a case statement
    A lot easier to add them by holding down the ALT key (ALT + mouse down) to insert them over multiply lines

    • How often are you troubleshooting that a comma is missing, really? Sorry, I'll still take less noise all the time.

      Also ALT + drag works over on the right, too, not just at the left margin.

  3. How dare you change my mind on this so easily…now I will be playing with SQLPrompt the rest of the day!