A pet peeve of mine is the lack of alias usage when using T-SQL. I’m not sure why people don’t bother using aliases –I’ve never asked- but I suspect its laziness. I can understand that but I want to speak up for the bastion of embattled SQL developers out there that have to do that much-feared task in an IT shop – debugging someone else’s code.
Take the following code:
select OBJECT_NAME(resource_associated_entity_id) LockedObj
, status
, login_name
from sys.dm_tran_locks
inner join sys.dm_exec_sessions
on request_session_id = session_id
where resource_type = 'object'
Its a query that provides information about locks on a SQL Server instance. Now many of you reading this know a lot about sys.dm_tran_locks and sys,dm_exec_sessions but equally a lot of people don’t and so it is not obvious which table/view the [status], [login_name], [resource_type], [resource_associated_entity_id], [request_session_id] and [session_id] fields actually come from. Would it not be better to write it like this?:
select OBJECT_NAME(l.resource_associated_entity_id) LockedObj
, s.status
, s.login_name
from sys.dm_tran_locks l
inner join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
where l.resource_type = 'object'
Of course it would. Ambiguity has been removed and the query is much easier to understand. Perhaps this example doesn’t strike a chord with you because you are pretty familiar with SQL Server’s DMVs but take a look at the following query that is indicative of some code I recently inherited:
select id
, name
, quantity
, address1
, address2
, city
, postcode
from Customers
inner join Orders
on id = s_id
and ref = 'QWERTY'
Where do all those columns come from? Its hardly clear is it? Are those address fields the customer’s address or the delivery address for the order? Which table has [id] as an identifier? [I’m actually simplifying the problem. There are a lot more tables and columns at play in the code I inherited]
So that’s my first gripe about aliases; If you don’t include them then the code is harder to understand. My second gripe is about misuse of aliases. Again, let’s take a look at some code:
update existingCustomers
set existingCustomers.name = newCustomers.name
, existingCustomers.address1 = newCustomers.address1
, existingCustomers.address2 = newCustomers.address2
from newCustomers
inner join existingCustomers
on newCustomers.id = existingCustomers.id
In this example the developer has prefixed all the columns with the name of the table rather than a table alias so now the same information is scattered throughout the query rather than in just one place. Debugging code often involves creating replicas of tables so that we can play around without affecting live data and in this situation its no inconceivable that I need to create a table called [existingCustomers_temp]. If I want to use the code above against my new table then I have to and make edits in six different places. If the code were written like this:
update e
set e.name = n.name
, e.address1 = n.address1
, e.address2 = n.address2
from newCustomers n
inner join existingCustomers e
on n.id = e.id
I only have to make one edit. In my opinion it looks a lot neater as well. I like conciseness where appropriate..
I implore you to use aliases and use them correctly. Especially if I am ever going to be inheriting your code!
-Jamie