Every now and then I see T-SQL code written like this:
select somecolumn as 'columnname'
Notice that 'columnname', despite its appearance, is not a string literal or a string expression, it is the alias that we are assigning to the column somecolumn
. Putting the name of an alias in single quotes in T-SQL is completely valid syntax, and it will work fine, but I feel that it is a really bad practice. It makes the distinction between string literals and object names very blurry, and it can lead to confusion when examining and maintaining your code.
For example, to me this is difficult to quickly look at and digest:
select 'literal' as 'columnname'
(1 row(s) affected)
It just makes your code harder to read, especially if syntax highlighting is used, since we no longer know at a glance what is a string literal and what is not. Of course, it gets even more difficult when you are dealing with long, complicated SQL statements.
It gets even worse when you consider that using "as" is optional when assigning an alias. For example, this is perfectly legal as well:
select 'literal' 'columnname'
(1 row(s) affected)
I don't know about you, but I would hate to inherit a database full of code like that!
Allowing the string literal delimiter to be used also implies to beginners that variables or other expressions can be used in place of column aliases, since they look like string expressions. Perhaps that is why now and then we see code like this:
select 'literal' as 'columnname' + @variable
That would of course make the misguided "generate dynamic column names" people very happy, but thankfully is not allowed.
Interestingly, while you can write column name aliases with the single quotes, you cannot alias tables or derived tables that way:
select * from
select 'jeff' as colname
) as 'test'
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'test'.
Which is certainly a good thing, but now perhaps a little inconsistent.
My advice? Don't delimit column aliases the same way you delimit string literals -- either do not delimit them at all, or use square brackets like [this]. This will help to distinguish your data from your code, and make things a little easier to maintain in the long run.