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:

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

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

See Also

SELECT