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:

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?