(While I can’t take credit for finding this issue originally — I heard about it several months ago — for the life of me I can’t remember or find where it was mentioned, so if you know of a reference, please leave a comment.)

When I first mentioned that developers should try to move away from using single-line comments (using –) and towards block comments (using /* */) in T-SQL, it was understandable that I got a bunch of “ummm… what?” looks.

All comments are created equal, right?

Well, obviously not, or this post wouldn’t need to exist.

Aside from the obvious differences, there’s a particular issue with single-line comments that’s indirect and somewhat subtle, yet it can be significant. If you’ve worked with SQL Server Profiler for any length of time, there is a decent chance you’ve come across this issue already. Whether you realized it or not is a separate question entirely.

Onwards.

The problem is that Profiler word-wraps captured SQL statements according to its window size. While that might seem okay at first glance, the fact is that artificially introducing extra newlines when the statement syntax depends on the arrangement of newlines… can lead to interesting things. Not only that, but the syntax highlighting is applied after the word-wrapping takes place, which exacerbates the problem.

Observe:

SELECT
	fulltext_catalog_id--, name, path, is_default, is_accent_sensitivity_on, data_space_id, file_id, principal_id, is_importing
	FROM sys.fulltext_catalogs

This query selects a single column from sys.fulltext_catalogs, while the other columns are commented out.

If I shrink the Profiler window (both to demonstrate the problem, and so screenshots nicely fit onto this page) and run the query, the statement appears like this:

That… doesn’t look right.

If you had only seen the query in Profiler and didn’t know what the original query was, do you know which query was actually executed?

The really dangerous thing is that if you inspect the erroneous-looking query closely, you’ll find that it is a different, yet syntactically valid, query.

SELECT
	fulltext_catalog_id--, name, path, is_default, is_accent_sensitivity_on,
data_space_id, file_id, principal_id, is_importing
	FROM sys.fulltext_catalogs

(If you don’t see it, this query aliases the fulltext_catalog_id column as data_space_id, and returns 4 columns in the result set.)

Here’s what you see in Profiler when you run it:

Looks familiar, doesn’t it?

Tips:

• Use block (/* */) comments wherever possible in production code, particularly if the comment occurs within part of a query. IMO, it’s easier and safer to just get in the habit of using block comments all the time rather than picking and choosing where to use each type, or having to teach this kind of esoteric information. I rarely take issue with personal coding style as long as code is legible, but this is an exceptional circumstance that can directly affect things beyond readability.
• Keep lines short, and break up long lines (this is a good practice for readability anyway). This can be accomplished in 99.999% of cases by using a combination of structured parentheses, indentation, and aliases.
• Any time you see a single-line comment (–) within the query text in Profiler, don’t assume what you’re being shown is what was actually executed. Copy the TextData cell (Ctrl+Shift+C) into a Management Studio query window to see the real query text.

(Note: the 2008 R2 version of Profiler is shown above; the 2012 version also exhibits this behaviour.)