Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've always seen and written my column aliases as

SELECT 1 as ColumnName

but today came across a query that used

SELECT ColumnName = 1

Is there any difference in how these two queries get executed? Or is there a standard among DBAs about which one to use?

Personally I think the 2nd would be easier to read/maintain for longer column definitions (good example here from this article), however I've never seen the 2nd syntax used before today so am wondering if there is some reason I shouldn't be using it.

share|improve this question
1  
Note that the assignment form of comment aliasing isn't portable, so although it makes things quite readable when you are doing a LOT of derived column work, it can be annoying to have to convert it later to another DBMS. –  Cade Roux Feb 26 '13 at 17:35
    
@Cade Celko makes that argument about certain things all the time. If I have to convert a SQL Server codebase to Oracle or PG or MySQL, alias composition will be the least of my worries. Since, unlike Celko, I have no interest in avoiding all proprietary features in case we ever move our entire architecture to a different RDBMS. I wonder how often that really happens outside of a classroom... –  Aaron Bertrand Feb 26 '13 at 17:59
    
@AaronBertrand I agree about mass conversion. I converted a bunch of stuff to Teradata and this is the least of the problems. What does continue to happen to me more often is I will import entire raw table data into my own SQL Server and make views or a query on it or whatever I do to analyze it, and then I will write a query or proc for the source system dialect to be called from SSIS or whatever to get JUST exactly the data I want across the wire. In those cases, I'm consciously writing very generic ANSI-style SQL. And then I still have to change things up like date functions. –  Cade Roux Feb 26 '13 at 18:22

2 Answers 2

up vote 14 down vote accepted

There is no difference in the underlying functionality of the two types of aliasing (as opposed to =). What it boils down to is exactly what you mentioned: Readability and maintainability.

In my opinion the former (<Expression> as <Alias>) is much more readable as it is self explanatory. When you have SELECT ColumnName = 1 I think it'd be pretty easy to mistake that as setting a variable on those long tired nights. You might mistake that as SELECT @ColumnName = 1 and that would be completely different functionality. Therefore, to circumvent any possibility of the query "double look", or even worse...error in understanding/coding, I go with SELECT 1 as ColumnName 100% of the time.

Personal preference, but consistency (for yourself and within your team) is king. Whatever you find easiest, go with and do it all the time. There is nothing more frustrating than switching back and forth for somebody troubleshooting/reviewing/maintaining code.

The third unmentioned way is to use <Expression> <Alias>. In other words, your second way without the as keyword. I think this is just as bad as the = symbol. It lacks readability at the gain of what? Not typing three extra characters (as and a space). Not worth it.

For exaggeration purposes, take a look at a query like this:

use AdventureWorks2012;
go

select
    [New Name] = Name,
    NewDepId = DepartmentID,
    GroupName as GName,
    ModifiedDate MyModDate
from HumanResources.Department;

Not code that I'd want to review.

share|improve this answer
5  
it always annoys me to see the last one, where they exclude the "as" keyword. Not required but a pain to read when looking at a bunch of code. –  Shawn Melton Feb 26 '13 at 16:47

Personally I find alias = expression easier to read and comprehend. The reason is that when I am troubleshooting a SELECT statement that has lengthy expressions, I probably want to find the expression via column name, not the other way around. Quick, find the expression that the application sees as alias2:

SELECT
  alias1 = (long expression with aggregates and multiple column references),
  (long expression with aggregates and multiple column references AS alias2
FROM ...

That is my preference. Yours may be different. There is no true advantage to using one or the other except for subjective / taste reasons. The important thing is that you pick one way to do it, and do it consistently (and unless you flip a coin, be able to defend your choice when you come up against someone who likes the other way). But if you write code for a DBA as fussy as me, be prepared for it to be rewritten. :-)

I've blogged about this.

One thing I feel even stronger about is the use of single quotes around alias names, e.g.

column AS 'alias'
'alias' = column

One form is deprecated but both are very difficult to read - and many newbies mistake the alias as a string literal, since that's what it looks like. For the same reasons, I absolutely detest the use of double quotes ("alias"). If you need to escape your alias because it is a reserved word or is otherwise poorly chosen or formatted, use [square brackets].

share|improve this answer
2  
Completely agree on the quotes aspect. As for the long expression, what I personally do is use carriage returns and newline tabbing and then put the as <Alias> on the last line of the column definition. But definitely agreed, it's as personal as how you like your coffee. –  Thomas Stringer Feb 26 '13 at 16:54
    
@ThomasStringer see I'd rather carriage return the expression. If a line starts off with AS Alias that AS is not very useful when I'm scanning vertically for a particular table name. I bet we'd disagree on where to put the commas too. :-) –  Aaron Bertrand Feb 26 '13 at 16:56
    
I tried using single quotes around my column aliases once because it made them Red which stood out in SSMS, but the annoyances of typing the quote key so much quickly became too much for me and I fell back upon my lazy ways. Also, it didn't work as well for its intended purposes when I had strings in my query. I'll probably stick with AS since that is what we use right now (I usually add a newline before AS ColumnName so they roughly line up), but I agree that = is much more readable in longer column definitions. –  Rachel Feb 26 '13 at 16:56
    
@AaronBertrand Do you put the commas in front of the column definition for every column after the first?? I don't think I've ever seen you do that. I keep my commas trailing the owning column definition. :-) –  Thomas Stringer Feb 26 '13 at 16:58
1  
@AaronBertrand I usually don't work with sensibly indented code >.< –  Rachel Feb 26 '13 at 17:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.