Table Aliases | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table Aliases

I have a basic question. Are there any performance benefits to using aliases and or full table names in a query? If the developers have named their table fields uniquely with a table identifier built in, is it necessary to also include an alias even though the field is not ambiguous? This is a contrived example. In both cases it is obvious where the data is coming from. I#%92m just so used to including the aliases, I wasn#%92t sure if there was a reason other then readability by the masses after the fact or if I was supporting some top secret performance enhancement [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Comments?<br /><br /><br /><br />i.e.<br /><br />SELECT <br />cust_Name, <br />ord_Name, <br />ord_Quantity <br />FROM db:confused:rders<br />INNER JOIN dbo.Customers<br />ON ord_customerId = cust_customerId<br />WHERE cust_state = ‘PA#%92<br /><br />Vs.<br /><br />SELECT <br />CUS.cust_Name, <br />ORD.ord_Name, <br />ORD.ord_Quantity <br />FROM db:confused:rders ORD<br />INNER JOIN dbo.Customers CUS<br />ON ORD.ord_customerId = CUS.cust_customerId<br />WHERE CUS.cust_state = ‘PA#%92<br /><br /><br />Patrick
No, you won’t gain any performance benefits from using aliases. Aliases are used for readability and to help future developers understand your code if you are not available. It is just a good coding standard. Generally, if you want to see if one query is faster than another, see the execution plan in query analyzer, and in your case, you shouldn’t find any differences.
As Tahsin said, there won’t be any difference in the execution plans. There *might* be a difference during compilation. However, I doubt that it’s significant. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
No performance benefits for aliases: they simply help in keeping the queries easy to read. And of course aliases let you use multiple instances of the same table, or create self-joins. There are performance benefits for including the owner prefix for every table/view in your query. And since you should also refer to each column as owner.table.column, again using the alias makes things a little easier to read. And did you know that you can use MyTable as an alias for dbo.MyTable? SELECT MyTable.MyColumn
FROM dbo.MyTable MyTable
]]>