Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Developers Please Use the Table Name/Alias to Prefix Columns

Note:  After I completed this post Aaron Bertrand added this subject to his “Bad Habits to Kick” series for myself and Jay as we suggested on Twitter.  I wrote this post because I knew Aaron was headed to the Winter Olympics so I didn’t think he’d get to it for few weeks and I needed an idea.  Thanks for getting to it, Aaron.
This post is almost an extension of Aaron Bertrand’s (@AaronBertrand) excellent “Bad Habits to Kick” series (if you aren’t reading his blog you should) and was inspired by this tweet:
image
Basically Jay was looking at a query like this (hard for me to write in SSMS because of SQLPrompt):
SELECT
Sales.SalesOrderHeader.SalesOrderID,
OrderDate,
ShipDate,
Status,
Sales.SalesOrderHeader.TerritoryID,
SubTotal,
TaxAmt,
Freight,
TotalDue,
Comment,
OrderQty,
UnitPriceDiscount,
LineTotal,
SP.SalesPersonID,
SP.TerritoryID,
SalesQuota
FROM
Sales.SalesOrderHeader JOIN
Sales.SalesOrderDetail AS SOD
ON Sales.SalesOrderHeader.SalesOrderID = SOD.SalesOrderID JOIN
Sales.SalesPerson AS SP
ON Sales.SalesOrderHeader.SalesPersonID = SP.SalesPersonID

Now, for the original person writing this code it makes perfect sense and they know which column belongs in which table, but, if there are performance issues and you call someone in to help, they won’t know what tables the columns belong to without looking at the schema.  As a matter of fact, the original author likely won’t remember the tables the columns belong to 6 months later either.  In my opinion the above query should look like this:




SELECT
SOH.SalesOrderID,
SOH.OrderDate,
SOH.ShipDate,
SOH.Status,
SOH.TerritoryID,
SOH.SubTotal,
SOH.TaxAmt,
SOH.Freight,
SOH.TotalDue,
SOH.Comment,
SOD.OrderQty,
SOD.UnitPriceDiscount,
SOD.LineTotal,
SP.SalesPersonID,
SP.TerritoryID,
SP.SalesQuota
FROM
Sales.SalesOrderHeader AS SOH JOIN
Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID JOIN
Sales.SalesPerson AS SP
ON SOH.SalesPersonID = SP.SalesPersonID

I know without going anywhere else what table each column belongs to and it is consistent.  I even think the Aliases meet with Aaron’s standard.


I know I can be a bit OCD with formatting T-SQL, but I like to be able to read the code quickly and at a glance.  See this post for my standards (I’ve actually slightly changed because SQLPrompt doesn’t, at least not that I’ve found, do EXACTLY what I like).


What do you think?

Comments

Posted by Jason Brimhall on 16 February 2010

Formatting code to quickly and easily be read by somebody else is tantamount to effective troubleshooting.  I find your revision to be similar to what I do.  And I find it much more readable.

Posted by Carl Federl on 16 February 2010

Another reason to fully qualify columns is that working code can break when a table change is made and then the existing code needs to be changed and then retested.

This table change will cause the SQL without alias to get an error:

Alter table Sales.SalesOrderDetail

add comments varchar(8000)

go

Posted by Jack Corbett on 17 February 2010

Great comments guys. I wholeheartedly agree with both.  

Jason,

I know the first thing I do is re-format SQL so I can read it more easily.  Now it's easy with the code-formatting feature of SQLPrompt or SQLRefactor(actually what I use).

Carl,

That's a great comment that really should be part of the post.  Aliasing and using the aliases properly will help prevent some breaking changes.

Leave a Comment

Please register or log in to leave a comment.