There are two ways you can write comments in T-SQL:
|
1 2 3 |
--By starting a line with two dashes, or /* By framing it on both sides like this */ |
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:
|
1 2 3 4 5 6 |
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar' -- This line is a comment AND Location = N'Iceland' ORDER BY Reputation DESC; |
Becomes this:
|
1 |
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar' -- This line is a comment AND Location = N'Iceland' ORDER BY Reputation DESC; |
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:
|
1 2 3 4 5 6 |
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar' /* This line is a comment */ AND Location = N'Iceland' ORDER BY Reputation DESC; |
That way, even if the monitoring tool or DMV dumps everything onto one line:
|
1 |
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar' /* This line is a comment */ AND Location = N'Iceland' ORDER BY Reputation DESC; |
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.)
138 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
SQL Prompt’s formatter is the dodgy app that doesn’t support double dash comments? That’s surprising, given that I use double-dash all the time and SQL Prompt’s formatter (10.13.12.1701) gives no problems.
What would be more surprising though, would be that they had an obvious, simple-to-fix bug and did nothing about it.
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)
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?
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.
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]
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! ?
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;
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.
YES YES I TAUGHT YOU SOMETHING
I AM TOTALLY QUITTING TODAY
Brent taught me to use the Clippy voice whenever I’m thinking about how execution plans are created..
No, that’s the stubborn, clueless dude. Clippy only recommends lol-indexes.
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.
— */ for the close, obviously.
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′}
Right, but it breaks monitoring tools and plan cache analysis tools like I explain in the post.
When you say “plan cache analysis tools” does that include SQL Server’s Query Store?
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.
I was wondering why this post was getting some attention! Add comment blocks with snippets technique:
https://dba.stackexchange.com/a/147182/11537
Underrated feature of SSMS.
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 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.
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 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.
— 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.
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
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.
?? 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.
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.
T-SQL supports double dash comments. Except some tools don’t. Are you THAT confident that those tools support nested block comments when they don’t support the easier double-dash ?
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
Jeff, RBAR respect)
What do you think about this arguments https://www.brentozar.com/archive/2021/04/never-ever-ever-start-t-sql-comments-with-two-dashes/#comment-3325494 and https://www.brentozar.com/archive/2021/04/never-ever-ever-start-t-sql-comments-with-two-dashes/#comment-3325496?
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.
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.
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.
Yeah, it’s worked at least for the last 5-10 years, I think.
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
*/
Nested comments still fail if it has a GO statement.
/*
/* This is a test
*/
SELECT 1 AS TEST;
GO
*/
It was really annoying since i use Go in a lot of commands. Now i got to change writing habits
It’s interesting that if you put GO inside a normal comment block, it’s ignored properly, but if you put it in a nested comment block you get an error. That error must be coming from the SSMS parser. This might be an issue with a block of code you send from an application that doesn’t pre-process it. SQL Server itself will safely ignore it, but monitoring tools might be tripped up by it still. But why would you comment generated SQL in an application in the SQL itself instead of the application code? I’m sure someone will find a reason…but it’s probably won’t be a good one.
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. 😉
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. 😀
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.
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!
— uninstalls SQL Server
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
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.
Don’t leave skid marks, use the toilet paper rolls /* */
Got it.
HAHAHA, love it.
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 😉
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.
Did you really just “well actually” me and then agree with me? That’s a new one.
Kind of but not quite. He agreed with you for a different reason (readability) not to compensate for flaws in the tools.
Waaaaaay too late
Perhaps it would be better, and easier, to get a few tool vendors and Microsoft to make their products work better than it is to prohibit every user everywhere from ever using ‘–‘.
Sure, go for it. I look forward to seeing your progress. Keep me posted.
And while you’re waiting for all those vendors to correct their tool, how will you comment your code in the meantime?
Heh… I have to join Brent in wishing you luck there. We’re talking about companies that think things like the SPLIT_STRING() and FORMAT() functions work well and don’t know that you don’t need to enable and disable a trigger to use things like DBCC PAGE or DBCC IND and they definitely don’t know the right way to maintain indexes, etc, etc, ad infinitum. And then there are those that use “Insert Bulk” (not to be confused with Bulk Insert) to do RBAR transactions.
Heh, with pun seriously intended, “dash it all… I have no further comments on this subject”. 😀
Never ever, ever start you stored procedure with comments before the name of the stored procedure. If you do, you will not be able to see the name of the stored procedure in diagnostic tools unless you can scroll. I have seen stored procedures with 10+ lines of comments before the “Create Procedure “
Surely ‘never ever’ is unnecessary.
It’s a fair point if you *have* to use tools that get confused, but clarity and productivity are also factors here.
I would steer well clear of tools designed for a language that can’t handle that language.
I sure wish I could dictate the tools that ran everywhere that my code was run. That’d be awesome. If you’re lucky enough to have a job like that, you should take a moment to thank your manager.
I do. Thankfully, that places me and a few others outside the “never ever” ?
Sure, as long as you never ever change jobs.
I would assume you would want to develop good habits before you need ’em, though.
I would argue using /* … */ everywhere is clumsy and unproductive rather than good practice. To my mind, that version of good practice is playing to the lowest common denominator, which could easily rule out any number of tools and techniques. Anything released to a wider audience dictates a target context.
The use of double dash comments certainly doesn’t affect SQL Prompt as far as I’ve seen, although the version I’m on insists on trying to parse double-dash comments until I press escape!
If by “playing to the lowest common denominator” you mean “getting things to work in as many places as possible,” then yes, I play to the lowest common denominator.
It sounds like we’re going to have to agree to disagree. Thanks for giving it a shot, though! Cheers.
As far as changing jobs is concerned… an employer has the right to set a balance between quality, cost and timescale. Part of that balance will necessarily impact the standards I undertake to create or obey – much as would prefer otherwise.
I’ve had enough jobs now to be able to adapt as I move between jobs, taking on board my employer’s priorities, but of course feeding back my own ideals and recommendations for best practice. The degree to which I push back depends on context – sometimes there’s a wider responsibility to legislation, self esteem or professional integrity. but I wouldn’t say this was one of those cases.
If you want your code to be universally acceptable, you’d better stick with SQL 2008R2 🙂
Indeed, I do – for example, in the First Responder Kit, we still strive for 2008 compatibility.
> First Reponder…
That’s reasonable, but I’m guessing that doesn’t translate to ‘never ever use anything else but 2008, so there’s a balance, not “never ever stray from 2008”.
Like you say, looks like we’re going to have to disagree, Thanks for giving it a shot, though!
BWAAAA-HAAAAA!!!! The really cool part about 2008 is that MS is no longer breaking things on it. 😀
All external tools aside here is yet another great reason to stop using one line comments.
SELECT * FROM Table1
WHERE
col1 = ‘a’
–AND col2 = ‘b’
AND col3 = ‘c’
AND col4 – ‘d’
An accidental back space at the beginning of the col3 line will cause that criteria to be part of the comment and not be applied anymore.
SELECT * FROM Table1
WHERE
col1 = ‘a’
–AND col2 = ‘b’ AND col3 = ‘c’
AND col4 – ‘d’
I can’t tell you how many times I’ve done something like that. And looking back at the code a day or two later it might not be all that obvious that that particular criteria is suppose to be commented or not. I work with a lot of data that comes from AS400 and field names and data values can be quite cryptic even with a good data dictionary.
The only time I still use single line comments is during development. When it’s ready for production, it’s cleaned up and all code and comments are well formatted.
You can’t protect everyone from making mistakes. If you make something totally idiot-proof, only idiots will use it. 😀 This is like saying that you shouldn’t have a disk sander in the house because someone might try to use it as a toothbrush. 😉
You can’t protect from every mistake, but if you can protect yourself from a rather costly one with a simple habit change, then why not? It’s like looking both ways when crossing the road. Yea, it’s really annoying that people run light, and those people are the real problem that needs to be fixed, but none of that is going to save you the moment you step off the curb and your path crosses with a speeding car.
Imagine that whether or not YOU look when you cross a particular street puts everyone else who ever crosses that street after you at risk of being hit by a car? Even better, what if when you looked when you crossed a road ensured that everyone who every crossed that street in the future was guaranteed to be safe? That’s what well formatted code does. I’ve spent years reviewing and refactoring code from developers who don’t really care about the stability of the code or the people who might have to maintain it later. As long as it works for them right now…that’s all they care about.
Your job as a developer to basically idiot proof, as much as is possible, business processes. When you make software that works well no one every says “That’s so easy only an idiot would use it!! So dumb!!” They say things like “I can’t believe I’ve been doing it the hard way all of this time…where have you been all of my life!” or “This is so much easier, now I can focus on my actual job instead of spending all of my time baby sitting this PoS software!” and “Please take my money!”
Who would have thought a post about SQL comment formatting would draw so much heat. xD
The habit I would change is to look at what your typing just like you should look both ways before you cross the street even if the light for pedestrian traffic is green… especially in areas where “Right turn on red” is allowed. 😉
I agree with the “easier” stuff but anyone that doesn’t realize they just backspaced over a couple of dashes needs to find a new career because idiot proofing dashes isn’t going to idiot proof the other 99.9% of the stuff they can screw up,.
“Look at what you are typing”
You’ve been doing this far too long to still never have been humbled by a accidental keystroke.
It’s not a “back spacing over a couple of dashes”. It’s a single backspace (or really any keystroke that replaces a newline with something else) on a line following a comment. It’s really easy to have your cursor positioned off screen and accidentally hit a key on your keyboard. When this happens you get absolutely no visual cue that anything has happened. You execute your code and it runs, but your results are bad, maybe not in an obvious way.
If you use /* */ most of the mistakes that you might make like this will either not change the logic of your code or it will break your syntax in an obvious ways that won’t likely execute. Syntax errors are easy to find and fix, logical ones are a nightmare and can take hours or weeks to solve.
Again…changing you commenting style isn’t going to fix every problem you might ever encounter, it’ll just fix the ones associated with that comment style. You’re basically throwing your hand up and saying “well we can’t fix everything, so why bother trying to address this one problem.”
If everyone who makes accidental keystrokes needed to find a new career there would be no one left…except you I suppose…because you never make mistakes. *shrug*
That’s precisely what I’m trying to get at, Nick. I’m not going to make a change in my documentation or commenting style just because someone down the road might make such a mistake.
If your not interested in creating a resilient and stable product that is built for the future then maybe you should find a new career. ??
Heh… yeah… lack of or presence of dashes so someone else might not make an typo are going to do that. Good luck, Nick.
I found double dash is useful when the commented text is also part of scripts for testing purposes, as follows:
– – select count(*) – – select * – – select distinct s.SaleType, s.DiscountType …
from sales s
join customer c on c.CustomerID=s.CustomerId
join product p on p.ProductId=s.ProductId
…
By selecting any part after – – and including any ‘join’ lines, partly or entirely, you literely have multiple test cases without breaking up the funcitonal scripts.
In dimensional model, you could have 20-30 dimensional joins on fact table. A very typical issue is over/double counted fact rows, and you need to find out which dimension join is responsible. All you need to do is to start dragging the cursor from select count (*) and include the dimensions you want to test and run the selected part. You may also conduct some data profiling by just selecting other commented parts.
If my comments are breaking your code, perhaps you should investigate your code? Also, it’s not “best practice” to use one comment style or the other. We can call it “Brent Preferences”
You’re saying there’s no such thing as best practices? Interesting. I see. Thanks for stopping by!
Not ones that are constructed from whole cloth for entertainment purposes – no.
You and I have very different ideas of entertainment, but hey, I’m glad I can at least entertain you. Cheers!
I know this is your site Brent, I’m in awe of the knowledge you hold, and I understand you can’t agree with everyone, but you don’t necessarily know what’s best in every context.
I’m going to suggest that edicts such as “never, ever do it this way” *may* not actually be appropriate, and if so, remarks such as “thanks for stopping by” and “thanks for giving it a shot” look kinda harsh, and tend to inflame and extend the conversation rather than close it down.
I *am* being harsh because you’re just disagreeing without providing evidence that your way of commenting is somehow better.
If you have evidence, great, bring it.
Otherwise, the door’s the X at the top right of your browser.
In my opinion, two dashes are at least twice as quick to enter, and don’t suffer wrap-round that an unclosed /* will bring. Plus the downside of compatibility with badly designed tools doesn’t exist in my context – I’m able to stick to tools that understand the language they’re supposed to support. So ‘never ever, ever’ doesn’t apply. Perhaps the article title should be “if you use these tools, don’t use double dash comments”?
Given your profile in the industry, perhaps you could bring some pressure to bear for the tools to be improved?
Btw, I have said all of the above before 🙂
Dave – I hear you that two dashes are easier for you. I hear you loud and clear. I want your life to be as easy as possible, and that’s great.
However, given your supreme intelligence and good looks, at some point, someone’s going to hire you into another company where you don’t have the control that you have now. At that point, you’re going to be disappointed at the way your code works.
I can’t say that again any other way. I’m hearing you, but you’re not hearing me. Best of luck on your journey.
Heh… This has been a really interesting thread. Brent is trying to explain how the real world puts the screws (again and again) to us and what to watch out for in this case. People really hate it when 3rd party code imposes more restrictions or does stupid things in an attempt to do good things. People hate it enough so that they try to “kill the messenger”.
Remember folks… Brent is just the messenger on this and his message is based on some fairly extensive experience.
Here’s more on monitoring software… a whole lot of them invoke Trace Flag 3604 to get the results of DBCC commands in table outputs. It’s because they don’t actually know about the WITH TABLE RESULTS option on the commands that will do it without the Trace Flags and so they fill your SQL Server Log with stupid stuff. There’s also a whole lot of them that use “Insert Bulk” (not to be confused with “Bulk Insert”) which creates a shedload of unused but allocated space because of the “fast insert” option that allocates a whole extent even for a small single row insert (thankfully, there’s a trace flag to override that but it’s global in nature).
You shouldn’t kill Brent about this double-dash warning he’s trying to give you any more than you should try to kill me for the other crap about monitoring software that I just told you about. 😀 He’s just telling you the crap that’s going to happen because people keep buying such things that are going to put such crap restrictions on you and it’s not always your choice. 😀
Thanks for the kind and observant words as always, sir.
Case study of double dash comment crashing SQL Server, courtesy of an Oracle linked server: https://docs.microsoft.com/en-us/troubleshoot/sql/admin/crashes-run-oracle-linked-server-query
— All good fun, most illuminating.
I totally agree with the post and the message – but tomorrow my right hand will do the damned double dashes again without asking its owner for permission ….
As a programmer I agree with you. Always use structured comments with an opening and closing tag, rather than unstructured such as –, #, ‘, or whatever….because parsers, the code that reads the code can work with it so much more efficiently and intelligently.
Here’s a fun one. Have a comment that contains a slash-star inside of a string literal:
DECLARE @X XML = ‘‘;
/*
For development purposes, this query will give you the inner XML values:
SELECT a.x.query(‘./*’) AS [InnerXML]
FROM @X.nodes(‘/root/a’) AS a(x);
*/
I’m sure there’s an alternate way to query, but when the comment is written like this, you get “Missing end comment mark ‘*/’.”
Definitely an edge case, so I’m not going to lose any sleep over it, but I just hit that today.
That @X had a value, looks like the comment engine stripped it out:
[root][a][b id=”1″ /][/a][a][b id=”2″ /][/a][/root]
I wish this was that easy..
Here is my complaint about that, I’ve been working in SSMS for years and never had problems using
/* This type of comments */
But when I put the code in other environments such as Azure Data Studio the following renders badly and results in a mess:
/*
Comment A1
/* Comment B1 */
Comment A2
*/
Comment A2 will not be shown as a comment and therefore will be shown as SQL script part of the actual code.
There are problems with nested comments and this is still not solved.
For anyone reading this in 2023, two dash comments do not mess up SQL Prompt as the author states. SQL prompt actually inserts the above mentioned style comments when autocompleting exec stored procedure. Sure, putting it in a single line will break it, but most of us will never run into a software that will do something like that
Don’t change your style to suit the tooling. Change the tooling to suit your style.
If the tooling does not support valid SQL, then change or modify it so that it does. It’s a much better solution then keeping everyone reminded to not use SQL the way it was meant to be used.
Exactly! And if your query won’t compile, change the tables to match your query. And if it still won’t compile, change the database server! You’re the one in charge here! Never surrender.
Exactly – I completely agree, Graeme. I’m not sure why Brent is so tightly coupled with a badly written tool that he’s willing to compromise coding standards to fit.
Because even tools made by MS will sometimes dump SQL as a string san-newlines. “I don’t like to write code this way” isn’t sufficient justification for a company to retool a bunch of stuff. Using this standard make this a moot issue, and cost nothing except a couple of brain cells.
It’s a bad case of the cart going before the horse. If a tool is substandard, don’t use it.
“Couple of brain cells” or not, if you don’t use the tool, why would you jump through unnecessary hoops?
Yeah! Dave’s right. Who cares what your coworkers or successors have to use? They’re on their own. If they don’t like two leading dashes, Dave doesn’t have the time to add another two characters to finish the comment.
I mean sure, the trailing */ method is actually less keystrokes when the comment is more than two lines long, but who cares about that? Dave doesn’t have the time to comment his code, either. That’s for suckers who care about their coworkers or successors – not Dave, baby! Life is short – Dave’s focused on the big man himself.
Go get ’em, Dave! Don’t listen to anybody else!
Surely you wouldn’t recommend open-close comments in any other programming language? No, because there isn’t a badly written tool forcing you to.
If someone wants to pass my code through a badly written tool in the future, they (or I) can very easily pass the code through a filter to convert one comment style to the other, so I get the best of both worlds.
Sarcasm and passive aggression are easy, but they’re not pretty.
Errr, most other programming languages also support open-close comments, especially for multi-line comments. C#, PHP, you name it.
Except you can’t, as I explain in the post. Next time, try reading it.
Good post, been bitten by this several times and never thought of changing my ways. Thanks!
Side note, would not have expected to see so many butt hurt from such a post.
You’ll always get kick-back if you tell people to ‘always’ or ‘never, ever’ do something ?