Should alias names be preceded by AS?

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

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.

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.