Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

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.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Is it a String Literal or an Alias?

Every now and then I see T-SQL code written like this:

select somecolumn as 'columnname'
from sometable

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:

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.

see also:

Print | posted on Thursday, August 30, 2007 11:36 AM | Filed Under [ Miscellaneous T-SQL ]

Feedback

Gravatar

# re: String Literals versus Alias Names

+1 on that one!
8/30/2007 12:54 PM | Mladen
Gravatar

# re: Is it a String Literal or an Alias?

What's your opinion on:

SELECT [thisIsMyColumnName] = someColumn
FROM someTable

?
10/12/2007 5:48 AM | georgev
Gravatar

# re: Is it a String Literal or an Alias?

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 ...

Is one style more ANSI-compliant than the other?
10/12/2007 8:48 AM | Jeff
Gravatar

# re: Is it a String Literal or an Alias?

I need to do the reverse.

I need to specify a string literal in a where clause, but it is also the name of one of the fields.

How do I specify the string literal so that it is not confused with the field of the same name?

select * from valid_codes vc_cs
where
vc_cs.vc_domain = 'purchasestatus'
AND
vc_cs.vc_code = ''

Here though, the table valid_codes also has a field called [purchasestatus] which is making the search fail. The field name cannot be changed nor the domain field text.

How do I specify 'purchasestatus' as a string literal.

I have tried double quotes also but then it fails with Invalid column name.

4/29/2008 11:34 AM | ow
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET