Bad Habits to Kick: Not Using "AS"

re: Bad Habits to Kick: Not Using "AS"

GilM — Fri, 09 Oct 2009 06:13:36 GMT

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

re: Bad Habits to Kick: Not Using "AS"

dan — Fri, 09 Oct 2009 08:40:50 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Adam Machanic — Fri, 09 Oct 2009 14:15:52 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Dinhduy Tran — Fri, 09 Oct 2009 17:45:42 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

David — Sat, 10 Oct 2009 08:37:01 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Dale — Sat, 10 Oct 2009 10:05:27 GMT

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

re: Bad Habits to Kick: Not Using "AS"

DM Unseen — Sat, 10 Oct 2009 13:18:56 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Sat, 10 Oct 2009 13:36:15 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

Rob — Sat, 10 Oct 2009 16:58:58 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Robert — Sat, 10 Oct 2009 20:18:42 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

AaronBertrand — Sat, 10 Oct 2009 20:45:22 GMT

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

Bad Habits to Kick: Inconsistent Indentation

Adam Machanic — Sat, 10 Oct 2009 21:39:56 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Agile BI — Sun, 11 Oct 2009 09:12:29 GMT

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!

re: Bad Habits to Kick: Not Using "AS"

Peter — Mon, 12 Oct 2009 09:04:52 GMT

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?

re: Bad Habits to Kick: Not Using "AS"

Madhivanan — Mon, 12 Oct 2009 14:00:12 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Brian Tkatch — Mon, 12 Oct 2009 14:36:20 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

Susan Van Eyck — Mon, 12 Oct 2009 18:06:16 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

Madhivanan — Tue, 13 Oct 2009 06:43:55 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Brianb — Wed, 14 Oct 2009 10:53:18 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

Mike — Wed, 14 Oct 2009 15:19:35 GMT

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

re: Bad Habits to Kick: Not Using "AS"

Johnny Mac — Wed, 14 Oct 2009 16:47:23 GMT

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.

re: Bad Habits to Kick: Not Using "AS"

Madhivanan — Thu, 15 Oct 2009 08:54:59 GMT

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