Is there any difference between putting a column alias at the start or the end of the column definition?

Question

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.

Answer 1

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.

Answer 2

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