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:
SELECT EmpSSN AS "Employee Social Security Number"
FROM EmpTable
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 OrderID,
DATEDIFF(dd, ShippedDate, GETDATE() ) AS DaysSinceShipped
FROM Northwind.dbo.Orders
WHERE ShippedDate IS NOT NULL
The AS clause is the syntax defined in the SQL-92 standard for assigning a name to a result set column. This is the preferred syntax to use in Microsoft® SQL Server™ version 7.0:
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 OrderID,
DaysSinceShipped = DATEDIFF(dd, ShippedDate, GETDATE() )
FROM Northwind.dbo.Orders
WHERE ShippedDate IS NOT NULL