I recently wrote a piece about the impact of table and column aliases on query performance and plan cache size. As I was writing the post, I recalled that there are multiple ways in which column aliases can be defined in a T-SQL query, depending upon the developer’s preference. However, one of these methods is marked for deprecation in one of the future releases of Microsoft SQL Server (it is still a valid method for SQL Server 2012, so there is no cause of worry in the immediate future).
The available methods for column aliasing and their status is shown in the table below:
|‘column_alias’ = expression||Deprecated|
|expression AS column_alias||Active|
|expression AS ”column_alias”||Active|
|expression AS [column_alias]||Active|
|[column_alias] = expression||Active|
|expression AS “column_alias”||Active|
All of these methods are demonstrated in the query below:
USE AdventureWorks2012; GO SELECT -- Format ; Status "Employee Birth Date" = Employee.BirthDate, -- "column_alias" = expression ; Deprecated Employee.HireDate AS JoiningDate, -- expression AS column_alias ; Active Employee.BusinessEntityID AS "EmployeeId", -- expression AS "column_alias"; Active Employee.OrganizationLevel AS [Org. Level], -- expression AS [column_alias]; Active [Salary Flag] = Employee.SalariedFlag, -- [column_alias] = expression ; Active Employee.SickLeaveHours AS "SickHours", -- expression AS “column_alias"; Active "VacationHours" = Employee.VacationHours -- "column_alias" = expression ; Deprecated FROM HumanResources.Employee AS Employee; GO
My recommendations around any such feature is:
- To have one standard for the entire organization/product
- The element adopted as the standard should not have been marked for deprecation for at least the next 2 major releases of Microsoft SQL Server
- Finally, and the most important consideration is that the feature should help the team become more productive and efficient
I personally use either expression AS [column_alias] OR [column_alias] = expression methods.
Before you leave, do share your preferred format for column aliasing.
Until we meet next time,