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