In my last post in this series, I wrote about ignoring the principle of least privilege, since a lot of people fall into the trap of following the path of least resistance. This time I wanted to touch on a topic I've touched on before: aliasing.
Over the weekend, there was a twitter conversation between
@unclebiguns and
@jaybonk (it started with
this post), and it reminded me of another bad habit I see quite a bit: inconsistent aliasing. Now, in this very series, I
talked about aliasing back in November. But in that case, I was talking about choosing poor and meaningless aliases like a, b, and c - and the problems that can cause when someone needs to make sense of the query later. In this case I am talking about bad practices involving being inconsistent with the use of table aliases. I'll illustrate with two examples:
Only aliasing some of the tables in the query
This is one form I see a lot, where an alias is used for some tables, but not all. You end up having short forms for table references and then in other spots you have to spell out the entire name of the table every time.
SELECT
o.OrderID,
OrderDetails.ProductID
FROM
dbo.Orders AS o
INNER JOIN
OrderDetails
ON o.OrderID = OrderDetails.OrderID
WHERE
o.OrderID = 1; |
If you have to maintain this query and it gets more complex later, you will be cursing yourself because you will have to write "OrderDetails" umpteen more times. Or you will be lazy, and also be guilty of:
Aliasing all tables, but not aliasing some of the columns
Another form of this pet peeve that I see is when the developer leads a wild goose chase, where we get to guess which table a column comes from. Of course the original coder knows what they were thinking, and they can get away with it because that column happens to only exist in one table mentioned in the query (at least for now).
SELECT
o.OrderID,
ProductID,
Quantity
FROM
dbo.Orders AS o
INNER JOIN
dbo.OrderDetails AS od
ON o.OrderID = od.OrderID
WHERE
o.OrderID = 1
AND ProductID IN (4,5,6,7,8); |
The problem is that if another table is introduced to the query (say, the Products table, to get the name of the products ordered), now you will have to go through all the column references to avoid ambiguous column name errors.
Summary
Basically I am against any type of shortcut where changing the query later will be much more cumbersome than it should be. For the sake of future maintenance, write your queries clearly, concisely, and consistently. As I've said before, I am not suggesting that you have to alias like I do, or that you have to alias at all... but if you're going to use table aliases, use them the same way every time.