My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

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'

columnname ---------- literal

(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'

columnname ---------- literal

(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:

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.

georgev -- I personally don't use that, not sure exactly why, maybe just out of habit. I think that it's fine as long as you are consistent. It can make your code more readable in that you can line up your column names on the left ...