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

Enhancing the readability of your code: Table aliasing in sql

By Sylvia Moestl Vasilik,

Aliasing tables can be a topic that database developers feel very strongly about. I'm actually one of the developers that do use aliases, but there's a right way to do it, and there's a wrong way.
First, the wrong way (using the pubs sample database):

select
c.Au_id
,c.au_lname
,c.au_fname
,a.title
,a.title_id
,b.royaltyper
from titles a
join titleauthor b
on a.title_id = b.title_id
join authors c
on c.au_id = b.au_id

Don't laugh. I've actually seen some SQL done exactly like this. Reading and understanding it later on takes far longer than it should (now what did b stand for again?).

Most people would agree that the aliasing in the above example is terrible, and most SQL isn't written that way. However, I'd say about 90% of SQL is written like the example below:

select
au.Au_id
,au.au_lname
,au.au_fname
,ti.title
,ti.title_id
,ta.royaltyper
from titles ti
join titleauthor ta
on ti.title_id = ta.title_id
join authors au
on ta.au_id = au.au_id

This is an improvement over using aliases that have no relation whatsoever with the table name. But in this particular SQL statement, there's no reason to alias your tables names - it would just save a few keystrokes, at the cost of making it much less readable. Here's what I would do.

select
authors.Au_id
,au_lname
,au_fname
,title
,titles.title_id
,royaltyper
from titles
join titleauthor
on titles.title_id = titleauthor.title_id
join authors
on titleauthor.au_id = authors.au_id

In this case - just a simple select statement - it really doesn't make any sense to use aliases.

It's in more complex SQL, with cross database joins, subqueries, and derived tables that you really want to use aliases in order to make your code more readable. For instance, the following from clause contains tables from 4 different databases:

from BookingStg.dbo.TSData TSData
join BookingStg.dbo.Booking Booking
  on Booking.BookingID = TSData.BookingID
join Domain.dbo.BookingWin BookingWin
  on TSData.DateOfStayTimeID =  BookingWin.DateOfStayTimeID
left join WarehouseFactStore.dbo.Customer_Dim Customer_Dim
  on Customer_Dim.TUID = Booking.TUID
  and Customer_Dim.ProductID = TSData.ProductIDleft 
join TServerImp.dbo.TLRPostalCode TLRPostalCode
  on TLRPostalCode.TLR = TSData.TLR
  and TLRPostalCode.ProductID = TSData.ProductID

When using tables from multiple databases, you alias the fully qualifed names (i.e. DatabaseName.SchemaName.TableName) with just the tablename. Like this:

join BookingStg.dbo.Booking Booking

This cuts down of the length of table references, without obscuring them.

Also, in a situation where you're creating a derived table, you'll need to pick a name for it. Make sure it reflects what the table is used for. For instance, here I'm creating a derived table in order to group transactions, so the alias name reflects that:


from #TransactionAdFact 
left join
(Select
TLR
,MinAdID= min(AdID)
from RetailOperations.dbo.OmTransaction OmTransaction
join #BaseData
on #BaseData.OmniProductMapping = OmTransaction.OmProductID
where
TransDateKey between @StartDateKey and @EndDateKey
and AdID is not null
group by
TLR
,OmTransaction.OmProductID ) GroupedTransactions
on GroupedTransactions.TLR = #TransactionAdFact.TLR

Please, don't call the derived table something like Temp1. I see this all the time. It may save you the minute it would cost to think of a name that would be understandable to other people, but for the sake of the people who will be reading this code - take the minute, and make your code more readable.

Chosing the right tables aliases in SQL Server can either make your queries far more readable...or you can save yourself some thought and a couple keystrokes. Your choice.


 

Update (5/20/2009)

Thanks for taking the time to read and comment on this article. Obviously this topic is one that people feel very strongly about! Some key points that I got from the comments:

-- Where I currently work, the tables are all well named, and the aliasing is confusing and makes code hard to read. However, there's also places where the tables are not well named, or are over 40 characters long. I can see that well constructed aliases can make the code more readable in that situation. I agree with the commenter who wrote:

For example, one of my central applications has table names such as TPB105_CHARGE_DETAIL and TSM180_MST_COD_DTL. It is much easier and faster to type, and frankly easier to read, if I alias those table names using "charges" and "codes".

-- Sounds like some people use tools like Red Gate's SQL Refactor to make code conform to a standard. That sounds like a good idea to me.

-- Some people just plain disagreed that aliasing makes things hard to read, and feel that using VA instead of VendorAddress should always be better, just because it's shorter. I think that what may be happening here is the "curse of knowledge". Because they know the subject area very well, it's hard to even imagine that it might be difficult for someone else to understand. There's a book called Made to Stick: Why Some Ideas Survive and Others Die, with a whole section on the "Curse of Knowledge". Very interesting read.

 

 

 

 

 

 

Total article views: 7828 | Views in the last 30 days: 4
 
Related Articles
BLOG

Random Thoughts on Aliasing

  Here are many different thoughts/facts/preferences on aliasing.  Most of this applies to column a...

FORUM

What is the behavior of aliased SELECT statements?

How does SQL Server internally handle SELECT statements aliased as tables?

FORUM

Enhancing the readability of your code: Table aliasing in sql

Comments posted to this topic are about the item [B]Enhancing the readability of your code: Table al...

ARTICLE

Tuning People?

Database people are used to changing the hardware of the server on which a problem database resides...

FORUM

Auto-Select Database Name

Auto-Select Database Name

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones