SQL Server Performance

Table aliases in SQL 2005 bad?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by bhushank21, May 24, 2007.

  1. bhushank21 New Member

    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
  2. dineshasanka Moderator

    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/
  3. MohammedU New Member

    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.
  4. Adriaan New Member

    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 ...
  5. satya Moderator

    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.
  6. Adriaan New Member

    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 ...
  7. bhushank21 New Member

    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
  8. Adriaan New Member

    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
  9. satya Moderator

    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.
  10. FrankKalis Moderator

    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>
  11. bhushank21 New Member

    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
  12. Madhivanan Moderator

  13. FrankKalis Moderator

  14. MohammedU New Member

    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.
  15. Madhivanan Moderator

    <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
  16. satya Moderator

    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.
  17. Madhivanan Moderator

    <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
  18. satya Moderator

    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>
  19. FrankKalis Moderator

    <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>
  20. Madhivanan Moderator

    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
  21. FrankKalis Moderator

    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>
  22. satya Moderator

    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">
  23. Madhivanan Moderator

    <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
  24. Madhivanan Moderator

    <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
  25. satya Moderator

    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>
  26. FrankKalis Moderator

    <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>
  27. Madhivanan Moderator

    <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

Share This Page