Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

    Unanswered: Best Practice For Aliases

    Hi,

    As I learn the basics, I'm trying as much as possible to get into the habit of best practice.

    Q1
    I've noted that when I'm creating an Alias, the "As" keyword is optional. Do people consider it best practice to include/exclude it, or does it really not matter at all?

    Q2
    When creating an alias name for a final report, if the alias name is a keyword or contains a wildcard or a space (etc), there seem to be a few different workarounds, eg -
    Code:
    [My Alias Name]
    'My Alias Name'
    "My Alias Name"
    Which of these should I get in the habit of using? For consistency, should I get in the habit of using them even if they aren't needed?
    Code:
    MyAliasName
    [MyAliasName]
    'My Alias Name'
    "My Alias Name"
    I did a google search and read a thread where someone recommended dropping the As keyword and always using [ ] around all aliases.

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Just a few thoughts:

    Whether text in double quotes are treated as text or identifier depends on the quoted_identifier option. If you write dynamic procedures, you'll have to double-quote aliases if you use single quotes. Aliases with square brackets does always work, even inside dynamic code. Thus I tend to use square brackets whenever I need to quote.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thanks. Based on your input I've decided to always use [ ] and always use the 'As' Keyword.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This begs the question of why you are aliasing in your sql code in the first place.
    Unless you are just linking to a spreadsheet, surely whatever reporting tool you are using has the capability of substituting its own column headers?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Are you never using functions or aggregations, and all your column names are unique across all tables and you never join the same table twice? There are lots of reasons to alias column names.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    At the moment, for 80% of the work I'm doing, I'm querying from Excel using ADO and importing directly into report workbooks. I could overwrite the report headers using VBA, but it's cleaner to do it in the SQL query.

    But I'm happy to put that point to one side because my question was about "general" best practice. Are you saying that Aliases shouldn't be used, Blindman? I'm a beginner at this and I'm keen to hear your views and will try to take your advice on board.

    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by roac View Post
    Are you never using functions or aggregations, and all your column names are unique across all tables and you never join the same table twice? There are lots of reasons to alias column names.
    He is clearly aliasing for the purpose of display formatting. Otherwise he would not feel the need to include space characters in his column names. Formatting of the data should not be handled in the database tier. The output from the database should be neutral in regards to formatting, so as to make it compatible with any reporting tool which needs to consume it.

    And since you ask, my column names are always descriptive and I always enumerate my column references. This makes my code clear and explicit without the need for aliases.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Colin,
    Aliasing results sets is klunky in Excel, so I guess I understand the need for this. Even so, you can perform your aliasing in the Excel query you use to pull from the database view or procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Okay - understood, thank you.

    I'll do some research on enumerating column references and post back if I have any questions about it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •