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:
Format | Status |
‘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
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,
You may also be interested to read this post where I showed N number of ways to alias a column name. http://beyondrelational.com/modules/2/blogs/70/posts/10928/different-ways-to-alias-a-column.aspx
My preferred method is col as alias
LikeLike
Thank-you, Madhivanan! That’s a very comprehensive collection of methods that can be used for column aliasing.
Also, thank-you for sharing your preferred method of aliasing. The expression AS [column_alias] has to be the most widely used method for aliasing.
LikeLike