Column alias in SQL Server – Always precede with keyword AS

In SQL Server, a column alias can be specified in several ways. Look at the following example

	100 number,
	100 [number]
	100              number,
	100 as number

The result is

number   number   number    number   number   number   number   number
-------- -------- --------  -------- -------  -------  -------  ------- 
100      100      100       100       100     100      100      100

Now as you see the column alias is number for all the expressions although they are all different expressions.

However you need to use AS before the alias name for better reading and standard

Now execute the following and see the result

drop table if exists #employee_master;
create table #employee_master
emp_id int, emp_name varchar(100), dob datetime
insert into #employee_master
select 1,'Sankar','19760910' union all
select 2,'Kamal','19680707' union all
select 3,'Rajesh','19661222'
Select emp_id, emp_name dob from #employee_master

The result is

emp_id      dob
----------- ------------
1           Sankar
2           Kamal
3           Rajesh

The SELECT statement lists out three columns but the output has only two columns. The data of emp_name are listed under the column name dob. It is because the missing comma between column names makes that dob becomes alias name for emp_name which is confusing and hard to detect if there are hundreds of lines in the procedure.

In my opinion, SQL Server should force preceding AS before column alias


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s