Never, Ever, Ever Start T-SQL Comments with Two Dashes
source link: https://www.brentozar.com/archive/2021/04/never-ever-ever-start-t-sql-comments-with-two-dashes/?utm_campaign=Feed%3A+BrentOzar-SqlServerDba+%28Brent+Ozar+Unlimited%29
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.)
54 Comments. Leave new
-
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
-
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.
-
Right, but now think about that in the context of this post…it still breaks. (sigh)
-
(FYI, I’m not arguing, I’m legitimately asking cause I don’t know, I’ve never used a monitoring tool)
This:
DECLARE @t char(2) = ‘U’
/*
DECLARE @t char(2) = ‘V’
–*/
SELECT *
FROM sys.objects o
WHERE o.[type] = @tTranslated 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?
-
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] = @tIf that’s why you’re saying it will still break, then that makes sense.
-
Bingo – when people uncomment the first part because they’re not testing, it’s broken in monitoring tools.
-
-
-
-
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]
ENDI 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! ?
-
I’m guilty of using that to toggle between two different lines of code.
/*
SET @Testing = 1;
–*/ SET @Testing = 0; /* this line runs */–/*
SET @Testing = 1; /* this line runs */
–*/ SET @Testing = 0;
-
-
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.
-
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.
-
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 :-/
-
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!
-
Never!
We’ll keep using line comments so that DBAs stop using crappy tools and MS fixes bad DMVs.
Period :-))
-
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 …-
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.
-
-
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.
-
SSMS suffers the same fate, unless you turn on the retain CR/LF option in the Query Result section.
-
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 bThat’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.
-
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.
-
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. -
Thank you for this — I have been doing this for years! Changing immediately!
-
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.
-
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.
-
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…
-
There isn’t one, but a new shortcut was added to VS 2019 for block comments. SSMS 18 uses the VS 2017 shell as far as I know, so it might automagically appear in SSMS 19.
However you can raise a request via Connect’s shiny new replacement, the Azure Feedback UserVoice! https://feedback.azure.com/forums/908035-sql-server
Pay no mind to the fact that the UserVoice is probably going to be shut down this year with (AFAIK) no word on whether there’ll be a replacement.
-
In fact a request was raised a couple of weeks back — https://feedback.azure.com/forums/908035-sql-server/suggestions/42990942-comment-the-selected-text-in-query-editor
There may have been hundreds of requests raised for this, but the search functionality is a little, er, useless.
-
Thanks, voted for it.
-
-
-
-
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.
-
/*
Neat
*/ -
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!
-
Strings /* with invisible characters like CR, LF & TAB */ are
— not
to be trusted! -
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 shortcutI 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.
-
— Your kidding right?
-
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.
-
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# -
This all just reinforces my belief that TSQL is not fit for purpose.
-
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 /*
-
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.
-
T-SQL supports nested block comments.
-
-
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 -
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. 😀
-
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
-
-
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.
-
I wouldn’t give it up just because someone says it’s “bad” for monitoring software.
-
-
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.
-
“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?
-
-
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.
-
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)
-
Hahaha, well, to be fair, my blog has quite a bit more subscribers than Paul’s. 😉
-
Leave a Reply Cancel reply
Your email address will not be published. Required fields are marked *
Notify me of followup comments via e-mail. You can also subscribe without commenting.
- Subscribe*
Hi! I’m Brent Ozar.
I make Microsoft SQL Server go faster. I love teaching, travel, and laughing. I live in Iceland with my wife Erika. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.
Want to advertise here and reach my savvy readers?
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK