Should alias names be preceded by AS?

If you ask me, I would say Yes Smile

When you write select statements and use alias names, always make sure alias names are preceded by the keyword AS. Oherwise you may get results which are unexpected and sometimes difficult to debug.

Consider the following example

use northwind

select employeeid,orderdate,shippeddate,shipcity from orders

Well. You see the correct resultset

Now run this

select employeeid,orderdate,shippeddate shipcity from orders

and see the resultset.Shippeddate's column name becomes shipcity because there is a missing comma after shippeddate that forces shipcity to be alias for shippeddate. If there are tens of columns and if you miss out a comma, it would become difficult to debug if the resultset is sent to the client application

Consider another example


select count(*) from orders

It returns the count as 830

Now what happens by mistake if you omit the keyword from


select count(*) orders

Now it returns count as 1 with alias name orders.

If SQL Server forces you to use AS before alias name, you would get error for the select statements that returns unexpected result in the above example. So I always ask developers to use AS before alias name which certainly makes the statements more readable and easy to debug

Also make sure you read this http://sqlblog.com/blogs/denis_gobo/archive/2007/11/13/3313.aspx

Published 14 November 2007 12:46 by Madhivanan
Filed under: ,

Comments

# re: Should alias names be preceded by AS?

14 November 2007 13:28 by tonyrogerson

One of the number of reasons why I use...

select alias = expression,

         alias2= expression2

from ....

Clear as crystal and you aren't using Ctrl F to find column names when you are debugging between the app and the database.

Tony.

# re: Should alias names be preceded by AS?

19 November 2007 09:03 by Madhivanan

Hi, Tony

I prefer using expression AS alias than alias=expression

Becuase it will work in all RDBMSs Smile

# re: Should alias names be preceded by AS?

23 November 2007 14:54 by DamianMulvena

Tony, I'd have said that the alias=colname is too readily confused with the version where you are assigning to variables. Even though the @ sign would make this clear, it takes that little bit longer to see whether this is an assignment select or a resultset select.

# re: Should alias names be preceded by AS?

27 November 2007 12:35 by AB

I prefer alias = column syntax, then when you are reviewing the column list, you can see the alias names all along the left, instead of staggered at the end of each line depending on the length/complexity of the original column name and/or expression.

# re: Should alias names be preceded by AS?

07 December 2007 11:03 by Madhivanan

Yes. As I said, I prefer AS alias name as it would work in all RDBMSs

# Should alias names be preceded by AS? - Part 2

09 September 2008 15:07 by Madhivanan

In the Part 1 , I expressed my opinion on having the alias names preceded by the keyword AS Adding to

# Should alias names be preceded by AS? - Part 2

09 September 2008 15:27 by SQL Server Transact-SQL (SSQA.net)

In the Part 1 , I expressed my opinion on having the alias names preceded by the keyword AS Adding to