It’s interesting to know about and think over some of the old-wives tales surrounding SQL Server and it’s features. One such tale is that the use of table and column aliases affects query performance.
The very purpose of table and column aliases is to improve the read-ability of the query, and nothing else. They help in eliminating ambiguity, but do not in any way impact the internal workings of SQL Server, and today I will be proving just that.
My example will be for using column aliases. You can work out a similar example for table aliases.
The Example
Below are two straight-forward T-SQL queries, intended for nothing else but to demonstrate the theory mentioned above. The first query does not use column aliases, whereas the second query uses them.
IMPORTANT NOTE: In this script, we use 2 DBCC commands – FREEPROCCACHE and DROPCLEANBUFFERS to simulate a cold buffer condition, that is generally achieved when SQL Server restarts. Doing so indiscriminately can have severe performance implications. Please do not execute the scripts shown in this example in your production or quality assurance environments.
USE AdventureWorks2012
GO
/*************************************************************************************************************
IMPORTANT WARNING
————————————————————————————————————–
DBCC FREEPROCCACHE and DROPCLEANBUFFERS are used to simulate a cold buffer condition,
that is generally achieved when SQL Server restarts.
Doing so indiscriminately can have severe performance implications.
Please do not execute the scripts shown in this example in your production or quality assurance environments.
The author, this site or Microsoft are not responsible
for any damage caused by indiscriminate use of these DBCC commands
*************************************************************************************************************/
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SELECT ””””Non-Aliased Query””””,
HRE.BusinessEntityID,
HRE.LoginID,
HRE.NationalIDNumber,
HRE.OrganizationNode,
HRE.OrganizationLevel,
HRE.JobTitle,
HRE.BirthDate,
HRE.MaritalStatus,
HRE.Gender,
HRE.HireDate,
HRE.SalariedFlag,
HRE.VacationHours,
HRE.SickLeaveHours,
HRE.CurrentFlag,
HRE.rowguid,
HRE.ModifiedDate,
Dept.DepartmentID,
Dept.ShiftID,
Dept.StartDate,
Dept.EndDate,
Dept.ModifiedDate,
HumanResources.Department.Name,
HumanResources.Department.GroupName,
HumanResources.Department.ModifiedDate,
HumanResources.Shift.Name,
HumanResources.Shift.ShiftID,
HumanResources.Shift.StartTime,
HumanResources.Shift.EndTime,
HumanResources.Shift.ModifiedDate
FROM HumanResources.Employee AS HRE
INNER JOIN HumanResources.EmployeeDepartmentHistory AS Dept ON HRE.BusinessEntityID = Dept.BusinessEntityID
INNER JOIN HumanResources.Department ON Dept.DepartmentID = HumanResources.Department.DepartmentID
INNER JOIN HumanResources.Shift ON Dept.ShiftID = HumanResources.Shift.ShiftID
GO
SELECT ””””Aliased Query””””,
EmployeeID = HRE.BusinessEntityID,
LoginID = HRE.LoginID,
NationalIDNumber = HRE.NationalIDNumber,
OrganizationNode = HRE.OrganizationNode,
OrganizationLevel = HRE.OrganizationLevel,
JobTitle = HRE.JobTitle,
BirthDate = HRE.BirthDate,
MaritalStatus = HRE.MaritalStatus,
Gender = HRE.Gender,
HireDate = HRE.HireDate,
SalariedFlag = HRE.SalariedFlag,
VacationHours = HRE.VacationHours,
SickLeaveHours = HRE.SickLeaveHours,
CurrentFlag = HRE.CurrentFlag,
rowguid = HRE.rowguid,
EmployeeModifiedDate = HRE.ModifiedDate,
DepartmentID = Dept.DepartmentID,
ShiftID = Dept.ShiftID,
StartDate = Dept.StartDate,
EndDate = Dept.EndDate,
DepartmentModifiedDate = Dept.ModifiedDate,
DepartmentName = HumanResources.Department.Name,
GroupName = HumanResources.Department.GroupName,
DepartmentModifiedDate = HumanResources.Department.ModifiedDate,
ShiftName = HumanResources.Shift.Name,
ShiftID = HumanResources.Shift.ShiftID,
StartTime = HumanResources.Shift.StartTime,
EndTime = HumanResources.Shift.EndTime,
ShiftModifiedDate = HumanResources.Shift.ModifiedDate
FROM HumanResources.Employee AS HRE
INNER JOIN HumanResources.EmployeeDepartmentHistory AS Dept ON HRE.BusinessEntityID = Dept.BusinessEntityID
INNER JOIN HumanResources.Department ON Dept.DepartmentID = HumanResources.Department.DepartmentID
INNER JOIN HumanResources.Shift ON Dept.ShiftID = HumanResources.Shift.ShiftID
GO
Impact on Query Performance
A quick way to compare the performance of two queries is to use the Actual Execution Plan. I therefore set the Actual Execution Plan to ON (press Ctrl + M) and then ran the entire batch (i.e. both queries) together against the AdventureWorks2012 database. The actual execution plan showed an equal – 50/50 split between the two queries.
Conclusion 01: Using column aliases does not affect query performance.
Impact on Query Plan Cache
Now that we have derived the conclusion that column aliases do not affect query performance, do they impact the plan cache? We know that query plans are ultimately text, and therefore a query using aliases should have a bigger plan, bloating the plan cache. The Dynamic Management Views provide a powerful way of checking this one out.
I wrote the following query in an attempt to find the query plan size in bytes for a given ad-hoc query, which is:
SELECT cp.size_in_bytes,
st.text,
qs.sql_handle,
qs.plan_handle,
cp.usecounts,
cp.refcounts,
cp.cacheobjtype,
cp.objtype
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
INNER JOIN sys.dm_exec_cached_plans as cp ON qs.plan_handle = cp.plan_handle
WHERE cp.objtype = ””””Adhoc””””
AND st.text LIKE ””””%HumanResources.EmployeeDepartmentHistory%””””
Which gives us:
As you can see, there is no difference in the cached plan size for both the queries.
Conclusion 02: Column aliasing does not impact the plan cache.
Conclusion 03: Last year, I wrote a piece on how writing your queries using different cases will cause SQL Server to create multiple query plans. Hence, it is advised to have a single, consistent standard towards using aliases in your product. You can read the post at: Query Plan Re-use: Write your T-SQL queries with the proper case for plan re-use and better query performance
Summarizing
Based on the observations shared above, I made the following conclusions:
|
Is affected by aliasing? |
Query Performance |
No |
Plan cache size |
No |
Plan reuse |
Possible |
What have been your observations with respect to aliasing? Do you use aliasing as part of your coding standards? Do leave a note as you go.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 