Tuesday, February 16, 2010

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?

2 comments:

  1. Even if I only have one table in the query I usually still prefix every column.

    ReplyDelete
  2. Jason,

    I actually do the same thing as well. Usually because I almost always end up adding tables to the query later.

    ReplyDelete

So what do you think I am?