Never, Ever, Ever Start T-SQL Comments with Two Dashes

There are two ways you can write comments in T-SQL:

Never, ever use two dashes. Never.

The problem is that monitoring tools and diagnostic management views often dump out the T-SQL in one long line. So this query:

Becomes this:

And you can’t tell where the query really ends. This REALLY causes problems with real-world-size queries with joins, CTEs, etc – stick a comment anywhere in the query, and you’ll break query-formatting tools like SQL Prompt.

Instead, frame out your comments:

That way, even if the monitoring tool or DMV dumps everything onto one line:

It’s still obvious what the query is doing, and T-SQL formatting tools can still rework it with proper line breaks.

I know some of y’all will want to lecture me about getting a better monitoring tool, but you wouldn’t believe what kinds of crappy tools are out there, and some DBAs just have to live with those tools. Frame your comments, and your successors will thank you. (I say “successors” not because they’re successful. I’m not saying they’re better than you. They just came after you, trailblazer.)

Previous Post
Free Columnstore Training Class on Tuesday
Next Post
Pop Quiz: What Do These Things Cost Today?

78 Comments. Leave new

  • Douglas Coats
    April 6, 2021 1:26 pm

    Its always the simple things that make the most impact. Lol

    Ive been doing this since i learned the hard way that redgate’s sql prompt and notepad++’s poor man’s sql Formatter both buckle to this.

    Great post

    Reply
  • Stuart Ainsworth
    April 6, 2021 1:36 pm

    One of my favorite commenting tricks is to use the framing method at the beginning, but add two dashes before the close.

    /*
    Testing code here
    – – */

    That way if I need to quickly uncomment the block, you can delete the first block and it works.

    Reply
    • Right, but now think about that in the context of this post…it still breaks. (sigh)

      Reply
      • Chad Baldwin
        April 6, 2021 2:49 pm

        (FYI, I’m not arguing, I’m legitimately asking cause I don’t know, I’ve never used a monitoring tool)

        How?

        This:

        DECLARE @t char(2) = ‘U’
        /*
        DECLARE @t char(2) = ‘V’
        –*/
        SELECT *
        FROM sys.objects o
        WHERE o.[type] = @t

        Translated to this:

        DECLARE @t char(2) = ‘U’ /* DECLARE @t char(2) = ‘V’ –*/ SELECT * FROM sys.objects o WHERE o.[type] = @t

        Both are still valid, and the double dash is ignored since it’s within the comment block?

        Reply
        • Chad Baldwin
          April 6, 2021 2:54 pm

          Unless you’re referring to when the inner test query is run.

          Like:

          DECLARE @t char(2) = ‘V’
          –*/
          SELECT *
          FROM sys.objects o
          WHERE o.[type] = @t

          If that’s why you’re saying it will still break, then that makes sense.

          Reply
    • Nick Fotopoulos
      April 6, 2021 9:11 pm

      I’ve become a comment stickler over the years.

      I always add my comments as
      /* my comment */

      And when I’m using those comments to delinate sections of logically connected commands I surround that block with a:

      BEGIN /* Step 1 – Do the thing with the stuff */
      [SQL CODE]
      END

      I can then collapse all code blocks and immediately see a mile high view of a mile long procedure and easily drill down into any section by expanding the code section. It’s beautiful! ?

      Reply
    • Brandon Champion
      April 6, 2021 11:00 pm

      I’m guilty of using that to toggle between two different lines of code.

      /*
      SET @Testing = 1;
      –*/ SET @Testing = 0; /* this line runs */

      vs

      –/*
      SET @Testing = 1; /* this line runs */
      –*/ SET @Testing = 0;

      Reply
  • Michael Swart
    April 6, 2021 1:37 pm

    This really makes me think.
    I’ve been happily using — for years and I’ve been bitten by the monitoring tools that assume that all whitespace (including newlines) are created equal and are interchangeable. I just never put the two together.

    It makes me wonder… Should the same reasoning apply to C# or Java? In that case no, the same reasoning you gave breaks down for those coding languages: We don’t have monitoring tools that mangle C#. There’s a compilation step that separates the code from the stuff that gets executed. And if we want to monitor that, we’ve got “symbols” so that it’s sufficient for those monitoring tools to report a call stack or a class or a method.

    Reply
  • Curtis Krumel
    April 6, 2021 1:43 pm

    Like Stewart wrote above, the two dashes before the close is a great time saver. I use it a little differently in that to uncomment the block, I add two dashes before the open:
    — /*
    T-SQL here
    –*/

    The T-SQL above is not commented.

    Reply
    • Curtis Krumel
      April 6, 2021 1:44 pm

      — */ for the close, obviously.

      Reply
      • Life Extinguisher
        April 13, 2021 9:44 am

        My thinks all crappy tools should at least be able to ignore comments.

        Like this PowerShell below

        ([String]::IsNullOrEmpty($_.Trim()) -or $_ -match “^\s–“)} |ForEach-Object {$_ -replace “(.)(–.*)”,’$1′}

        Reply
    • Right, but it breaks monitoring tools and plan cache analysis tools like I explain in the post.

      Reply
      • Nick Fotopoulos
        April 13, 2021 3:24 pm

        When you say “plan cache analysis tools” does that include SQL Server’s Query Store?

        Reply
  • Thx for useful post about this small, but important topic.

    It’s sad that the double dashes are even generated by SMSS when using the built-in feature for comment out selected lines :-/

    Reply
  • Søren Delövenbo Kongstad
    April 6, 2021 2:20 pm

    SSIS had an issue with that type of comment as well. If you used SQL Query in a Source component, it would query the database to get the field names and data types of the data set.
    However in some circumstances it would use sp_describe_first_result_set, as far as I remember, and pass the query as a string removing line breaks!

    So amongst other things, good a nd bad, using /* .. */ comments in tsql was taught to me the hard way by SSIS!

    Reply
  • Never!

    We’ll keep using line comments so that DBAs stop using crappy tools and MS fixes bad DMVs.

    Period :-))

    Reply
  • Eric Russell
    April 6, 2021 3:56 pm

    When doing something like returning SQL text from a DMV query or event trace, I will leverage CAST AS XML to preserve all the line feeds, so it works with “—“ style comments. Another advantage is that from grid view I can simply click on a column value to open the text in a separate window.
    For example:

    SELECT …
    CAST(” AS XML) AS SqlXml
    FROM …

    Reply
    • convert(xml, [XmlText], 1) will preserve all the original white space. Cast and convert without the parameter will add its own line breaks and indentation between nodes that don’t have text (or maybe that is SSMS adding it). You will still have the problem if whatever created the text has already stripped the line breaks and whitespace.

      Reply
  • Is there a way to use Ctrl-K, Ctrl-C to do this in SSMS? Or another shortcut in SSMS?

    I found an article regarding doing this using snippets, but that is as ideal for me. I would prefer to do this using a shortcut.

    Reply
  • Adrian Procter
    April 6, 2021 4:14 pm

    SSMS suffers the same fate, unless you turn on the retain CR/LF option in the Query Result section.

    Reply
  • Kevin Fries
    April 6, 2021 4:17 pm

    I’ve always used “–” inside of a SSMS or other “studio” tools strictly for one off queries that benefited from leaving out pieces for testing purposes.

    select a,b from c
    where a = ‘abc’
    — order by b

    That’s by no means a finished product and it’s much easier to test with. (I have several “studios” for databases of all types.) Not all will allow you to highlight a specified query area as a running context as SSMS does. Some insist on “–” or “/* */” and it’s painful.

    Reply
  • Michael Smith
    April 6, 2021 4:33 pm

    Saw this and laughed as I just ran into an issue a few weeks back that was a result of this. Not sure what library or tool our devs were using, but there were several queries that they had written in toad and then pasted into their code, keeping the multiline format they were written in. Whatever library they used parsed it into a single line and the double-dashed comment made the rest of the query commented out. It lead to incorrect results and for some reason (didn’t take time to research the why since it was “import to fix fast”) it also made the queries take between 3 and 4 minutes to get results.

    All I did was replace the “–” and wrap the comments with “/*…*/” and the queries were now executing correctly and just over 1 minute.

    I’ll probably just make it a practice to never use “–” for anything other than ad hoc querying.

    Reply
  • Thiago Timm
    April 6, 2021 4:48 pm

    Awesome Brent!
    I know that is not the point of the post, but when this comments /**/ has multiple lines, I’ve already see the replication of a trigger change goes really… I mean… Really wrong! (kind of the same reason… the replication chop the *really-long-trigger-script to propagate, and cut right in the middle of the comment).
    * Sql Server 2005 transactional replication by the time.

    Reply
  • Thank you for this — I have been doing this for years! Changing immediately!

    Reply
  • Timothy Sceurman
    April 6, 2021 5:01 pm

    Does it depend on the tool? It’s been over a year since I was responsible for monitoring stuff, but I am fairly certain when I would look at queries in that section of the monitoring tool we used, the code was properly formatted and I never had a problem with comments (SentryOne, formerly SQL Sentry).

    That being said, I do like consistency, and this makes sense. I just always did /* ——— COMMENT ———— */, because it looked nice to me, and called my eye to comments and sections of code, far more than –. And I’m all about making code as easy to skim as possible.

    Reply
  • Mark Allison
    April 6, 2021 5:03 pm

    Makes me wonder about PowerShell as that is not a compiled language. However, I don’t recall ever using a monitoring tool against PowerShell code.

    Reply
  • It would be nice if SSMS let you configure the comment buttons to use block-style instead of line-style comment indicators (I can’t find such a setting, but if anyone knows of one, please post it…).

    Hmmm, might be a good feedback request… but I see Connect no longer exists…

    Reply
  • brenda grossnickle
    April 6, 2021 5:14 pm

    I stole a piece of tsql code awhile back that removes everything between a — and char(13) and or char(10). everytime i use the code i keep my fingers crossed, but it how always worked for what i needed.

    Reply
  • Kristofer Olafsson
    April 6, 2021 5:21 pm

    /*
    Neat
    */

    Reply
  • Michael Furey
    April 6, 2021 5:38 pm

    I have been chastised now and hang my face down in shame. Thanks for that Brent.
    I add quick line comments for myself and others all the time. Even just –MF 4-6-21 to indicate when I’ve changed something. Then I turn around and complain when RedGate SQL Compare doesn’t actually ignore comments when I’ve selected that option.

    Anyone have an idea of how to replace all –line comments in an entire database (procs, functions, views) with /* comment */ ? The */ needed at EOL seems like it could pose a challenge beyond an easy multi-file replace. thx!

    Reply
  • Strings /* with invisible characters like CR, LF & TAB */ are
    — not
    to be trusted!

    Reply
  • I’ve never had SQL Prompt choke on line comments, and I’ve never seen a DMV/DMF remove linebreaks (the SSMS results grid removes them, which is why I rap the knuckles of people I see copying out of the results grid for anything important — there’s an SSMS option to preserve linebreaks when copying and saving, but it isn’t on by default and I treat SSMS results as a visualisation and nothing more).

    But this is an admirably simple workaround for problems that do exist (which includes people assuming SSMS hasn’t mangled their results, which is a fairly reasonable if sadly incorrect assumption to make).

    What I would say, though, is to follow the usual steps after implementing a workaround and at least attempt to address the root cause or implement more systemic workarounds:

    * file a bug report with the folks that make your monitoring tool or whatever if it does this, especially if it’s claiming to be a parser
    * ask MS to change the default value of the SSMS option to preserve linebreaks on copy/save
    * ask MS to change Ctrl-K/Ctrl-C and Ctrl-K/Ctrl-U to use block comments instead of line comments, or an option to choose which, or a separate block comment/uncomment shortcut

    I probably wouldn’t go so far as to ask MS to officially deprecate line comments, because even if they did they wouldn’t actually implement it as a breaking change (they simply don’t do that — that’s what CUs are for!) and VS proper (eventually) introduced a block comment shortcut in 2019. Though it might not be the worst idea either — I’d take consistency + no havoc over choice + havoc when it came to my SQL.

    Reply
  • — Your kidding right?

    Reply
  • The bad news and SQL, as far as I’m concerned, is that people don’t put in comments 🙁

    The double dash and end of line pair is part of the ANSI/ISO standards. We got it from IBM, who invented it for use with punchcards. It’s so much easier to pull the comment cards out of the deck with this convention than it is to try and match bracketed pairs. The slash star notation is from the C programming language; this is because the C programming language started on mag tapes (well, actually, on paper tape but that’s another story). They both have some technical problems. The double dash can be read as two minus signs. SQL allows unary plus and minus operators, so the double dash is technically ambiguous. Likewise, in C, a slash is a division operator and a star has to do with pointer references. There were early C compilers which got confused with this decades ago, so you had to be careful about your spacing.

    Algol 60 had an explicit comment statement, which began with the keyword “comment” and was terminated with a semicolon, like all the other statements in this language. Algol also allowed comment text between the keyword “end” and its terminating semicolon. The same convention was picked up by PL/1. The joke at the time was that PL/1 was so smart they would read the code and read the comments; if the code was wrong, it will compile the comments.

    Reply
  • David Tovee
    April 6, 2021 7:50 pm

    This article is a bit old but following on the comments above, I’m going to try this tomorrow to see if I can get the hotkeys working…

    SSMS shortcut to comment/uncomment a specific part of the query?
    https://dba.stackexchange.com/questions/147165/ssms-shortcut-to-comment-uncomment-a-specific-part-of-the-query#

    Reply
  • This all just reinforces my belief that TSQL is not fit for purpose.

    Reply
  • Simon Holzman
    April 6, 2021 8:53 pm

    Brent, sorry mate, but you are wrong this time. Changing the way you write code because a tool sucks is not the optimum solution to the problem that the tool sucks. If the tool sucks in how it handles comments, what else does it suck at doing that you are only going to find out about when the brown, smelly stuff has been fully distributed by the wind maker ?

    Your advice will reduce the amount of comments that are used, which will lead to less understandable code.

    Like most programmers, I frequently use single line comments to enable or disable particular conditions or to explain what a condition is doing (… AND Provider_ID = 37 — Dr Smith)

    The problem with using /* routinely instead of — is that the first */ will end the next comment it hits; The /* … */ comment cannot be nested.

    Therefore, if you are commenting out a chunk of code, your comment could end much sooner than you expect.

    Better to keep the two dashes for the single line isolated comments and use the /* … */ only for bulk comments or the initial information section of the code.

    One related suggestion… whenever using /* … */ you should actually end the comment with –*/

    This will allow you to uncomment the code by using two dashes to comment out the initial /*

    Reply
    • Simon – interesting, so you would also refuse to change code if you had a SQL Server performance issue then? Would you say that the only acceptable answer is to force Microsoft to change, and refuse to change your code?

      Interesting choice of hill to die on there. Good luck with your pursuits. I’ll be over here adapting to work with the tools that I’m given. To each their own.

      Reply
      • Thomas Franz
        April 7, 2021 2:09 pm

        I agree to Simon. If there are tools that have problems with those comments, then this tools should be fixed. Everything else would be the same, as if you recommend me to always open my car with the physical key instead of pushing the remote control button, because there may be another car on the park deck that uses the same frequence or because the remote control sometimes not works correctly, when I am to far away or behind a iron fence or whatever)

        BTW: which tools / DMVs are you talking about? I never had those problems, except I work on a PC where the CRLF-Option in SSMS is not set, but this are just a few clicks to “repair

        Reply
      • Julian Mummery
        April 7, 2021 9:33 pm

        I wasn’t expecting a childish response like that. In my opinion Simon is spot on.

        3rd party tools need to adapt or die. Its not the 3rd party tools that dictate what the syntax does, and the development cycle of the database should NOT adapt for 3rd party needs unless absolutely essential. This is not one of those reasons.

        Maybe there is a market for a tool that actually works instead of forcing change the wrong way around! Then anyone bothered by it would use it? Just a thought.

        Reply
        • Konstantin Taranov
          April 8, 2021 7:28 am

          ?? guys, Julian Mummery and Simon Holzman.

          I don’t want to be the judge of this argument, but I want to show you that Brent only showed the tip of the iceberg. The development of IT systems in today’s world is so complex that SQL code can be encapsulated at any stage of the application – in the Django application, in bash build scripts, in Go delivery code (the list is endless). Every element of this system can format code and what do you think can go wrong when it sees lines of SQL code that are just lines to it?

          On our team, we have a special rule (https://github.com/ktaranov/sqlserver-kit/blob/master/SQL Server Name Convention and T-SQL Programming Style.md#sql-comment) about commenting on SQL code – and we choose security over convenience.

          Reply
          • Konstantin – exactly. I’ve hit this problem with homegrown monitoring tools, commercial products, even security appliances that logged running queries.

            I wish I could lay down the law and say, “Well, that tool isn’t good enough, and the entire company needs to adapt to my code,” but Lord knows that doesn’t work in enterprises.

          • I’m interested in the “security” aspect you speak of. Are you saying that the double-dashes in code are a security issue or are you saying that the security monitoring tool has an issue with them? If the latter, I’d seriously consider finding a better tool. If they haven’t accounted for inline comments in code (whether it’s in T-SQL or some other language), what else have they not considered and are missing?

            Again, just an opinion/suggestion…

    • T-SQL supports nested block comments.

      Reply
  • my preference is to not put comments at the top/beginning. I will do something like:
    SELECT xxx
    /* comment */
    FROM Table ….
    the reason is dm_exec_query_stats statement_start_offset begins after the last statement. I want my query to dm_exec_query_stats to show the start of the SQL and not the comment

    Reply
  • Lordy… if you have a “monitoring” or “diagnostic” tool that can’t tell where inline (double dash) comments start and end, then what else can’t it do? I certainly wouldn’t trust one that loses it’s mind over such a thing. If I bought one by mistake, I’d be asking for my money back.

    In the same vein, I avoid box comments because if you have to view code somewhere where no color is applied and someone has left several hundred lines of commented out code, you can’t easily tell which code is commented using Box Comments. When there’s were a double-dash at the beginning of each commented out line, it’s a whole lot easier.

    Yeah, I know… a “different country” heard from and just another 2 cents from someone else but that’s my opinion. 😀

    As for people that leave hundreds of line of zombied code in stored procedures, there may be a pork chop launcher in your future. 😀

    Reply
    • Yeah, stuff that’s mangling code from DMVs and claiming to be a specialist SQL Server tool probably needs a supplier rethink.

      I hate code left (by design) in comments in sprocs. If you find yourself debugging your procedures so often that you have to leave chunks of code lying around to make it easier for you when they break in prod every five minutes, you should probably go do some training. Put your tools in a text file on your desktop if you must, don’t make me peer-review them

      Reply
    • Konstantin Taranov
      April 8, 2021 7:38 am
      Reply
      • This first post you sited has a link that’s broken. Heh… must be a double-dash problem. 😀 😀 😀 They do mention “security” but there’s not much else about that because of the broken link. They also mention code that reformats code for encapsulation??? Really? That’s a bit nuts.

        On the second post you reference, I agree with Brent that it’s frightful that we have no control over the code that other tools impose on us. That’s also a part of the reason why I try to limit using different 3rd party code just because someone has a particular itch. I use inline double-dash comments just about everywhere except for the flower box header in the T-SQL I write. About the only place I’ve found where the inline form of comments causes problems is on forum replies, surprisingly even on some forums dedicated to most SQL Server. I’ve never run into a problem with using inline comments anywhere else.

        I can see double-dashes being a problem with SQL Injection but, lordy… that doesn’t apply to inline documentation in stored procedures.

        Of course, that’s just my opinion. Depending on what has been heaped upon others, YMMV.

        Reply
  • I am new to SQL and in the process of self taught. I always use the — for commenting. But I won’t be doing it anymore. Thanks for this tip.

    Reply
  • Poor monitoring software, I’d say. Never use monitoring/diagnostic tools that change original code (why the effort and riskt?) and then even don’t do it right. I do my job right, they should do theirs right.

    Reply
    • Nick Fotopoulos
      April 7, 2021 12:24 am

      “And you can’t tell where the query really ends.”

      Even a not-so-long query without comment quickly becomes impossible to read on one line. How useful is reading a long query a’la single line anyways?

      Reply
  • Can’t say I agree. The problem with block comments in SQL and same in other languages is they don’t nest/unnest properly. Comment out a block of code that contains comments and you’ve got a problem.

    Reply
    • And of course tested it, and nesting does now work. Been writing SQL a long time, and it certainly didn’t used to. And it doesn’t in most languages that support block comments. It’s good that it does though.

      If you want to do it in SSMS easily, a “surrounds with” snippet should work. Will try to make one and post it.

      Reply
      • Yeah, it’s worked at least for the last 5-10 years, I think.

        Reply
        • I’m using Azure Data Studio these days, and nested comments work for the query parser, but not for the UI. This query doesn’t run, but the UI only turns the first four lines green, not the whole thing:
          /*
          /* skip person check for now…
          Get first ten person rows:
          */
          select top 10 * from dbo.Person
          */

          Reply
  • Konstantin Taranov
    April 7, 2021 9:08 am

    Brent, you are forgot main reason – your blog comment section can not handle properly SQL single comment ‘–‘! It converts into ‘—’

    Also nice confirmation of the Parkinson’s bicycle-shed effect (https://en.wikipedia.org/wiki/Wikipedia:Avoid_Parkinson's_bicycle-shed_effect): Brent post “Never, Ever, Ever Start T-SQL Comments with Two Dashes” – more than 50 comments, Paul White post “How Parallel Plans Start Up – Part 1” – 2 comments)

    Reply
    • Hahaha, well, to be fair, my blog has quite a bit more subscribers than Paul’s. 😉

      Reply
    • Heh… I didn’t know the name of the effect but that’s a good one. The bicycle-shed and refreshments effects are why I normally don’t take part in forums or other discussions where the solutions being offered are based on “opinion” or “personal experience” (of course, there are exceptions for trusted/verified sources) and stick mostly to things where code is the proof. I guess I’ll have to go back to that. 😀

      Reply
  • Aaron N. Cutshall
    April 7, 2021 1:04 pm

    You know Brent, I’m surprised at you. You are not one that I considered to promote absolutes. I admit to being wary of statements that say “Always do this” or “Never do that” unconditionally. With SQL Server, there are always caveats and conditions that make absolute statements nearly impossible. For me, I generally follow “rules of thumb” instead that have a good explanation of those caveats and conditions.

    Reply
    • Ah, you must be new around here! Try this syntax with Google:

      never site:brentozar.com
      always site:brentozar.com

      And welcome to the club! This is a really lively blog with a lot of posts where I grab folks by the shoulders, shake ’em, and get ’em to think about alternative viewpoints. Enjoy!

      Reply
  • — uninstalls SQL Server

    Reply
  • Wow you people saying your customer or enterprise will have to change their other tools to suit your programming style just amaze me. ….
    You do know that even Microsoft removes CR/LF in SQL code when you embed it in other things. I wrote some T-SQL then pasted it in Power Query (the sql is embeded in the M Language). I knew Power Query striped the CR/LF, but never thought about comments. I found my own — comments in there…. and realized the challenges it creates. Power Query does make it easier adding a #(lf), but still an issue.

    For those that don’t know Power Query is used in Excel, Power BI, Power Query Online and Data Flows and I think Azure Data Factory.

    In other words…. the issues Brent brings up are in many places. …. I will let you tell you people they can’t use Excel anymore.

    …. will is totally top using — for comments? Probability not, but I will be more selective when and how i use them ….

    Will i change ALL my SQL code, probably not, but

    Reply
    • Heh… you also have to look at it the other way around… there are a bunch of tools that require us to change our coding style… and it’s a common coding style using long-in-the-tooth functionality. Why don’t people write tools that will handle such a thing?

      I do, however, strongly agree with the premise that this thread has made me keenly aware of such things. It won’t change my coding style for the most part but I’ll now always make sure to test for both inline comments and CR/LF issues when someone introduces yet another tool to our environment.

      Reply
  • Rex Henderson
    April 8, 2021 6:22 pm

    Don’t leave skid marks, use the toilet paper rolls /* */
    Got it.

    Reply
  • Koen Verbeeck
    April 12, 2021 5:01 pm

    In SSIS and Oracle this can also lead to big troubles:
    https://sqlkover.com/ssis-and-the-ora-00907-missing-right-parenthesis-error/

    (using those two in one sentence must make you shudder 😉

    Reply
  • Well, it is actually the tools that interpret the code in a bad way as both LF and CR may be part or the code. Still I agree on encapsulating comments adds to readability.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu