Bad Habits to Kick: Not Using "AS"

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

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

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.

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

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

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

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.

,

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

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.  

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

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

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!

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?

Not Using AS is not only less readable, but leads to error also

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

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/09/should-alias-names-be-preceded-by-as-part-2.aspx

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.

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.

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

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.

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

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.

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