Table aliases in SQL 2005 bad? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table aliases in SQL 2005 bad?

Hello, Recently I saw that in one of the data models, column names were prefixed with table names.
For e.g. The Employee table had the columns named as
EmployeeGender
EmployeeAddress
EmployeeBirthDate and so on. The db designer said that the table name prefix helps to avoid table aliases in the join queries where some columns may be common to the joined tables. I would like to know if Table aliases impact query performance in SQL Server 2005. Regards,
Bhushan
Table Aliases are to improve the readbility —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Table alias will not effect any performance in 2000/2005… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

quote:The db designer said that he had prefixed with the table name to avoid table aliases in the join queries where some columns may be common to the joined tables.
The whole point of using identical names for columns that have a FK relationship is that it is easier to find the matching columns for a join when you’re writing queries … (Of course it’s different when you’re dealing with ‘generic entities’ that are involved in loads of different FKs.) Two rules for referring to objects in queries: (1) Always include the owner prefix, even if it is dbo, except if you need the flexibility of referring to the copy of the object owned by the current login. (2) Always use aliases for the objects in the FROM clause to improve readability, and add the proper alias before every reference to a column in your query. If you don’t, that reduces your chances of execution plans getting reused, which can be bad for performance. It also makes it easier to troubleshoot …
Bhushan
Have you observed any sort of changes in the performance in this case, like using with and without?
I have observed that sort of behaviour at my end when used within complexed queries. (addition to the reply after more digout in this regard)
Within TSQL qualified all columns in a statement with their appropriate table names are for readability, also performance is counted in few cases that uses joins of more than two tables. The more tables that are being joined, the more difficulty you’ll have figuring out which table each column comes from. By qualifying each column with the table name, it is better for optimizer too for a better decision on execution. Fyi, aliases will be removed from next release of SQL Server, I know its very early to talk but as a caution better not to use them now. BOLhttp://msdn2.microsoft.com/en-us/library/ms143729.aspx confirms same too on the deprecated features.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Satya, I think you’re jumping to conclusions, it’s just one version of an alias that will no longer be supported:
quote:A string enclosed in quotes used as a column alias for an expression in a SELECT list: ‘string_alias’ = expression
On the Replacement column, you see the versions that will remain supported:
quote:expression [AS] column_alias
expression [AS] [column_alias]
expression [AS] "column_alias"
expression [AS] ‘column_alias’
column_alias = expression
Note that the alias between single or double quotes, and the alias with the = sign, remain supported, just not the alias between single quotes with the = sign! However, they don’t mention what is happening with the alias between double quotes with the = sign …
Thanks!
I have not yet tested with and without table alias.
It seems that thehttp://msdn2.microsoft.com/en-us/library/ms143729.aspx link is speaking about the removal of "sp_addalias" (mapping user to a login).
It does not say about table alias.
Could you please explain if you meant something else? Also, is it necessary to use the Table name prefix in the column name? (I am speaking about column naming convention). Qualifying the column with the table/alias name ofcourse helps in readability.
Thanks and Regards,
Bhushan Thanks and Regards,
Bhushan
Aliases can be short – one character is already enough, or you can ‘summarize’ the name. SELECT one.col
FROM dbo.tblOneLongTableNameThatYouDoNotWantToRepeatInWholeQuery one
INNER JOIN dbo.tblAnotherLongTableNameThatYouDoNotWantToRepeatInWholeQuery another
ON one.fk = another.rk
WHERE another.col = 12345

Bushan
I agree with Adrian in bit confusion on alias, my reference is not applicable in this case. Check on the reference of readability in the reply and confirm.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
You’re correct. The link satya gave does not mention the removement of table aliases. AFAIK, this feature is not at disposal (and won’t probably ever be, if you ask me). <br /><br />It is not necessary to use the tablename.columnname construct as long as columnnames are not ambiguous. But when you provide the tablename.columnname you help the parser a bit here. Otherwise SQL Server might need some computational cycles more to find out itself. That’s where table aliases come into play as a means of helping both you (readability) and the engine (resolving names). <br /><br />I doubt that aliasing really has a serious impact on performance, but I’m always willing to learn. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Thank you MohammedU, Satya, Dinesh, Adriaan and Frank! Yes Satya, your readability point is valid. So we have to either use
<AliasName>.<ColumnName>
or
<TableName>.<ColumnName> Table Name can be used as a qualifier when required. Thanks and Regards,
Bhushan
More discussions regarding use of aliases
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81222 Madhivanan Failing to plan is Planning to fail
quote:Originally posted by Madhivanan More discussions regarding use of aliases
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81222 Madhivanan Failing to plan is Planning to fail
I wouldn’t call this a discussion. At least not for posts beyond the 2 – 3 page.
Occam’s razor, methinks. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
In SQL Server we have different types of aliases but I don’t see anything going away any time soon for table aliases…. Is there any Peformance related issues to discuss on this? IMHO there is nothing… If any one has experience perfomance issues using table aliases, please post the code and explanation…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />More discussions regarding use of aliases<br /<a target="_blank" href=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81222>http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81222</a><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I wouldn’t call this a discussion. At least not for posts beyond the 2 – 3 page. <br />Occam’s razor, methinks.<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. I meant <b>discussions</b> related to use of Alias only [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />what does <b>Occam’s razor, methinks</b> mean?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Madhi, that means:
One should not increase, beyond what is necessary, the number of entities required to explain anything. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Madhi, that means:<br />One should not increase, beyond what is necessary, the number of entities required to explain anything.<br /><br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thanks Satya. I didnt know that [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Even I too knew that today only [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />,<a target="_blank" href=http://en.wikipedia.org/wiki/Google>http://en.wikipedia.org/wiki/Google</a> is our friend.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />More discussions regarding use of aliases<br /<a target="_blank" href=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81222>http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81222</a><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I wouldn’t call this a discussion. At least not for posts beyond the 2 – 3 page. <br />Occam’s razor, methinks.<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. I meant <b>discussions</b> related to use of Alias only [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />what does <b>Occam’s razor, methinks</b> mean?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Check out my fav. quote in my profile and thereafter Wikipedia or something similar. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Good to see your picture [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />When did you upload?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Must have been some years ago. [<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Where is yours?<br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Good to see your picture [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />When did you upload?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Must have been some years ago. [<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Must have been some <b>days</b> ago. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Where is yours?<br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Good to see your picture [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />When did you upload?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Think about your old <b>Latest News</b> [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Don’t hide and come out cleanly here [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Must have been some years ago. [<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Must have been some <b>days</b> ago. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Either way…where is your pic?<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Must have been some years ago. [<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Must have been some <b>days</b> ago. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Either way…where is your pic?<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />See my previous reply [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
]]>