Home Uncategorized Bad Habits to Kick: Not Using "AS"

    Bad Habits to Kick: Not Using "AS"

    By
    Adam Machanic
    -
    678
    21

    Aaron has recently been doing an absolutely fantastic series of posts detailing various “bad habits” that many of us pick up somewhere along the way. These coding anti-patterns aren’t going to crash your server, but they will make your code more difficult to read and maintain. I’m enjoying Aaron’s series so much that I’ve decided to join in the fun with one of my own.

    The “AS” keyword is optional both for table aliases and column name aliases. And although I’m pretty good about using it for columns, I’ve only recently started trying to break myself of the habit of not using it for tables. Let’s take a look at some code:

    [sql]SELECT
    c.FirstName + ‘ ‘ + c.LastName FullName,
    c.Address,
    o.OrderDate,
    o.Subtotal + o.Shipping Total
    FROM Customer c
    INNER JOIN Order o ON
    o.CustomerId = c.CustomerId
    WHERE
    o.OrderId = 10[/sql]

    This code isn’t the most difficult thing in world to read, but I’ve tried to follow my ideal formatting practices in every way except for the AS keyword. This is a simple query, yet even here I find that my eye is scanning for something to differentiate the column names and the alias names. The AS keyword makes the eye snap to exactly the right spot when required:

    [sql]

    SELECT
    c.FirstName + ‘ ‘ + c.LastName AS FullName,
    c.Address,
    o.OrderDate,
    o.Subtotal + o.Shipping AS Total
    FROM Customer AS c
    INNER JOIN Order AS o ON
    o.CustomerId = c.CustomerId
    WHERE
    o.OrderId = 10 [/sql]

    For me this code speaks for itself. It’s just a tiny bit easier to read–enough that after a long day of coding I would probably make fewer mistakes maintaining this code than the previous version.

    Another option is use of “=”, but I don’t like that for three reasons:

    1. It can’t be used everywhere. It only works in the column list, and not for table aliases. This means that I now have to use two different systems in my queries if I want to be complete.
    2. “=” is the assignment operator. It’s also the equality operator. Do we really need to give it yet another overload?
    3. It seems more logical to me to define what I’m naming, then name it. Using “=”, you’re doing the opposite. Here’s my name, and here’s what it is. Kind of like the difference between a LEFT OUTER JOIN and a RIGHT OUTER JOIN. And 9 out of 10 SQL developers seem to prefer the former, for good reason. That’s just the way our minds work.

    Breaking yourself of this habit won’t take a lot of additional effort, but it just might pay off some late night when you’re forced to do a blurry-eyed emergency fix on some key piece of code and you don’t mess it up on the first shot. You can thank me the next morning.

    RELATED ARTICLESMORE FROM AUTHOR

    21 COMMENTS

    2. Lol @ GilM.
      Also enjoying the series.
      One minor point on column aliasing is that when maintaining/investigating other peoples’ code, when you are trying to find where a field name is populated it can be a nightmare when the field names are not listed below each other.
      YMMV…

    3. That’s what I get for blogging late at night. Even worse, I slipped and made the table names singlular–I actually prefer plural.

    4. Personally, I use only AS when:
      1. There are many tables involving in the query
      2. Table names are too long (poorly named)
      3. Self-reference, no other choice in this case 😉
      The bottom like is "always" uses AS so that it becomes "a habit" is not a good idea.

    6. I’m also loving these articles; mainly because I completely by accident happen to be doing it the right way so far. Still gonna keep reading over more of these to see if there is anything I’m not doing properly! Thanks for the article 🙂

    8. Personally, I find the ‘=’ assignment allows for much for readable code. The AS puts the name your assigning to the end of the line, where the = allows all the custom column names at the beginning. The difference between reading this:
      SELECT
        schema.table.shortcolumname AS A,
        schema.table.verylongcolumnname + anotherverylongcolumname AS B
      and reading this:
      SELECT
        [A] = schema.table.shortcolumname,
        [B] = schema.table.verylongcolumnname + anotherverylongcolumname
      The difference is huge to me, I can scan down the query rows and easily find the column I’m looking for. It’s even more helpful with CTE and it’s easier to find the column name to reference than scrolling back and forth in the window.
      ,

    9. @M – I totally agree, the column list is defined at the left and the (sometimes long) source details trail off to the right.  Most often I want to see what columns are returned when re-reading an old query.  many derived columns are the product of a complex calculation, the details of which are less often as valuable as the returned column name to the reader.

    10. I also agree with M on using ‘=’ for column name aliases.  I find it much easier to snapshot the column names at a glance when formatted this way.  That being said…  I think this is ultimately true for most formatting styles.  The key is that once you consistently format in a certain manner, you are able to visually snapshot the relevant sections in your query very quickly.  

    11. Yes, I am of the = vs. AS camp, because I like the column names to be on the left rather than scattered depending on how length of the expression it is derived from.
      But as Robert says, it is more important to be consistent than to randomize; neither of these is "right," but one of them is "right for me" and so I always use it consistently.  ðŸ™‚

    12. I’m with "M" on this one, I find using  Alias = Column much easier to read, I will also ensure the code is Tabbed appropriately to line up each "=".  The worst case is definitely when neither is used and it isn’t obvious what’s going on at all!

    13. I agree with the use of ‘AS’ for table aliases. The distinction is even more clear with syntax color highlighting in SSMS. And because of this now commonplace feature in every language I also go with lower case keywords. Because with syntax color highlighting I find a lowercase word is more natural to read then an uppercase.
      As for use of the ‘=’ to assign column names, I recall that when I saw it the first time I was perplexed. I found it in old but very basic SQL code and it was causing a weird problem in our environment. I never bothered to get to the bottom of it and switched to the use of ‘as’ and the problem solved.
      Somehow to this environment there was a functional difference between ‘AS’ and ‘=’. This has to originate somewhere, maybe the results are transferred differently? I really don’t know.
      SO my quetion is, is the use of ‘=’ really a standard or just a quirk that for some has gotten an entierly novel use?
      I do use it sometimes to test update statements with a from clause. I simply comment out the update and put a select above the fields and I can see what feeds the update. I figured this is it’s intended use, as it is convenient and almost tailor made.
      Can somone clarify the status of ‘=’?
      Are there restrictions, like only in the outermost select, etc?

    15. I never use AS. I find it clutters the code and makes it harder for a quick read. So much so, that if when working on a query i reformat it (helps me tremendously) i remove all "AS"s.
      To me, if a SQL statement is formatted in an easy to read manner, AS is redundant. If it is not formatted in an easy to read manner, AS won’t help.
      But to each their own.

    16. I’m also in the ‘AS’ as clutter corner.  My convention is to enclose column aliases in single quotes so they pop in my editing tools (red in SSMS):
      SELECT c.FirstName + ‘ ‘ + c.LastName ‘FullName’,
      And I reserve ‘=’ for assigning values to variables.

    18. Totally agree about the consistency part, but I never use "AS" or "=". Instead I use square brackets around my column names [FullName]. I like this better because it’s very easy to spot in your code, and if your output needs to go directly to a client you can use more client friendly names [Full Name] as an example.

    19. Personally, I prefer to line thing up (have to use a font like courier new though). I also like commas at the begining of a line instead of the end. This way, if I remove a column, I do not have to go back up to the previous line to delete the comma at the end. here is an example:
      SELECT c.FirstName + ‘ ‘ + c.LastName     FullName
           ,c.Address
           ,o.OrderDate
           ,o.Subtotal + o.Shipping            Total
       FROM Customer        c  INNER JOIN
            Order           o
         ON o.CustomerId = c.CustomerId
      WHERE o.OrderId    = 10

    20. With the rise of the datagrids in applications and since most applications are targeted at Windows one must be extremely careful to make sure there is a ‘logical’ flow as to the references for column names both in SQL and the consuming application.
      One of our strictures is that the Sql column name is the ‘root’ name.
      What I mean by this is if I have a GridColumn for say FullName then then GridColumn itself is named gcFullName the data reference for the GridColumn is FullName and anything else associated with the FullName such as functions contains FullName in it’s specifications.
      Now, when referencing a concatenation of say FirstName + LasName it is critical that it is aliased as FullName for this type of convention to work. I can’t really reference FirstName + LastName, SQL Server returns the column name as null.
      Why reference the column’s name? It makes it independent of it’s ordinal position in the query’s output which, when the code is enhanced for whatever reason this usually prevents breakage.

    21. Mike, you may need to re-read my previous comment that tells you the type of error you get when you omit a comma after column name

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here