#0170-SQL Server-Deprecated Features-Column Alias defined by a string enclosed in quotation marks


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,

Be courteous. Drive responsibly.

Advertisements

2 thoughts on “#0170-SQL Server-Deprecated Features-Column Alias defined by a string enclosed in quotation marks

  1. Nakul Vachhrajani

    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.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s