Assigning Result Set Column Names

The AS clause can be used either to change the name of a result set column or assign a name to a derived column.

When a result set column is defined by a reference to a column in a table or view, the name of the result set column is the same as the name of the referenced column. The AS clause can be used to assign a different name, or alias, to the result set column. This can be done to increase readability. For example:

USE AdventureWorks2008R2;
GO
SELECT BusinessEntityID AS "Employee Identification Number"
FROM HumanResources.Employee;

Derived columns are those columns in the select list that are specified as something other than a simple reference to a column. Derived columns have no name unless the AS clause is used to assign a name. In this example, the derived column specified using the DATEDIFF function would have no name if the AS clause were removed:

SELECT SalesOrderID,
       DATEDIFF(dd, ShipDate, GETDATE() ) AS DaysSinceShipped
FROM AdventureWorks2008R2.Sales.SalesOrderHeader
WHERE ShipDate IS NOT NULL ;

The AS clause is the syntax defined in the ISO standard for assigning a name to a result set column. This is the preferred syntax to use in SQL Server 2005.

column_name AS column_alias

or

result_column_expression AS derived_column_name

Transact-SQL also supports the following syntax for compatibility with earlier versions of SQL Server:

column_alias = column_name

or

derived_column_name = result_column_expression

For example, the last sample can be coded as:

SELECT SalesOrderID,
       DaysSinceShipped = DATEDIFF(dd, ShipDate, GETDATE() )
FROM AdventureWorks2008R2.Sales.SalesOrderHeader
WHERE ShipDate IS NOT NULL