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
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)
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)
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'.
(
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:
- Some SELECTs will never return 0 rows -- regardless of the criteria
- By The Way ... DISTINCT is not a function ...
- A handy but little-known SQL function: NULLIF()
- Is it a String Literal or an Alias?
- Passing an Array or Table Parameter to a Stored Procedure
- SELECT * FROM TABLE -- except for these columns
- In SQL, it's a Case Expression, *not* a Case Statement
- Returning Random Numbers in a SELECT statement
Legacy Comments
|
Mladen
2007-08-30 |
re: String Literals versus Alias Names +1 on that one! |
|
georgev
2007-10-12 |
re: Is it a String Literal or an Alias? What's your opinion on: SELECT [thisIsMyColumnName] = someColumn FROM someTable ? |
|
Jeff
2007-10-12 |
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? |
|
ow
2008-04-29 |
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 = ' ' |