THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Bad Habits to Kick: Not Using "AS"

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:

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

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:

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

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.
Published Thursday, October 08, 2009 11:33 PM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

GilM said:

How about the bad habit of not qualifying table names with the schema names?

October 9, 2009 2:13 AM
 

dan said:

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...

October 9, 2009 4:40 AM
 

Adam Machanic said:

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

October 9, 2009 10:15 AM
 

Dinhduy Tran said:

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.

October 9, 2009 1:45 PM
 

David said:

I hadn't realised it was optional!! I am learning lots of new bad habits in the series.

October 10, 2009 4:37 AM
 

Dale said:

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 :)

October 10, 2009 6:05 AM
 

DM Unseen said:

Not all SQL Dialaects support AS, so when coding DBMS agnostic queries, you should drop the "AS" keyword

October 10, 2009 9:18 AM
 

M said:

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.

,

October 10, 2009 9:36 AM
 

Rob said:

@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.

October 10, 2009 12:58 PM
 

Robert said:

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.  

October 10, 2009 4:18 PM
 

AaronBertrand said:

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.  :-)

October 10, 2009 4:45 PM
 

Adam Machanic said:

My last post in Aaron's series drew a mixed review from some readers, and I'm sure this one will do the

October 10, 2009 5:39 PM
 

Agile BI said:

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!

October 11, 2009 5:12 AM
 

Peter said:

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?

October 12, 2009 5:04 AM
 

Madhivanan said:

October 12, 2009 10:00 AM
 

Brian Tkatch said:

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.

October 12, 2009 10:36 AM
 

Susan Van Eyck said:

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.

October 12, 2009 2:06 PM
 

Madhivanan said:

Those opposing the usage of AS, read the first reply I posted

If comma is omitted, next column become alias for previous column

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/14/should-alias-names-be-preceded-by-as.aspx

October 13, 2009 2:43 AM
 

Brianb said:

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.

October 14, 2009 6:53 AM
 

Mike said:

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

October 14, 2009 11:19 AM
 

Johnny Mac said:

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.

October 14, 2009 12:47 PM
 

Madhivanan said:

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

October 15, 2009 4:54 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement