Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Alias Naming Conventions

Let's go to the mailbag!

In response to this post, Chris writes:

Jeff, I just got done reading your VB posts. I love those sarcastic posts you do.

Anyway, my coworker and I disagree on something, and I'd like to consult you to "settle an argument". At work, I'm told to not use semantic table aliases. Just like you said, I'd normally use ct.OrderID for CustomerTransactions.OrderID, but instead I'm told to use a.OrderID.

I have to use a, b, c, etc at work because that's just what they do here and I'm not going to argue about it with anybody. But as a personal disagreement with my coworker, he thinks that naming the tables a, b, c in order that they were joined is more clear for debugging than actually naming them something based off of the table name, in other words, semantic.

To clarify, he'd do:

SELECT b.OrderID, a.CustomerID, c.OrderDetailID
FROM
Customer a
JOIN Order b on a.CustomerID = b.CustomerID
JOIN OrderDetail c on b.OrderID = c.OrderID
WHERE
{ some conditionals here }

That's a contrived example to demonstrate our use of table aliases, not a real production example.

Will you provide some guidance on this?

Chris, I personally take your side on this. Naming anything based on the "order in which things are done" is a bad idea, in my opinion, because often we need to change the order in which things are done and/or remove or add some steps as we revise and edit our code. If the join no longer needs table B, do we edit the entire thing to "cascade down" table alias C to B, D to C, and so on?

I think it's pretty clear that this:

SELECT CustTotals.CustomerID, CustTotals.Amount, ....

is eaiser to read and understand in a large SQL statement rather than this:

SELECT D.CustomerID, D.Amount, ....

It's all subjective, of course, but that's my take. Granted, the alias "D" is shorter than "CustTotals" and easier to type, so sometimes I might even just use "C" or "CT" for "customer totals". At least there is some meaning behind it and at a glance it is easy to interpret in my opinion. It might be quite confusing to alias "Customers" as "B" and "Budgets" as "C" just because they happened to be joined that way!

Of course, I do use meaningless aliases as well (often, just "A" or "tmp" or even "X") but usually only when there is a single derived table that I am "wrapping". i.e., something like this:

select a.*
from
( select customerID, transactionID, Rate, Units, Rate*Units as Amount
from Transactions ) a
where
a.Amount > 0

In that case, putting a meaningful alias on the inner SELECT would be redudant, since it would be the same as the outer SELECT; we are just using the derived table to make it clearer or shorter or because we need to reference an inner calculation more than once. So, in those cases, I tend to use just a quick one-letter alias. If I were to join that inner derived table to another table, though, I would give them both meaningful aliases to keep things clear:

select  
Cust.CustomerID, Cust.Name, Trans.TransactionID,
Trans.Rate, Trans.Units, Trans.Amount
from
Customers Cust
left outer join
( select transactionID, Rate, Units, Rate*Units as Amount from Transactions ) Trans
on Cust.CustomerID = Trans.CustomerID
where
Trans.Amount > 0

Does anyone else have any other feedback on this issue? Any other conventions that work well for you? Let us know!


see also:


Print | posted on Monday, July 31, 2006 10:01 AM | Filed Under [ Miscellaneous Techniques ]

Feedback

Gravatar

# re: Let's go to the mailbag!

My personal convention is first 1 or 2 letters of the table if it's one name (Clients C) or every first letter (AddressObject AO) etc...
i use t1, t2, tn for temp tables and tv1...tvn for table variables.
7/31/2006 10:13 AM | Mladen
Gravatar

# re: Alias Naming Conventions

OMG....if this was ever up anyone's alley, it would be the blind dude...a,b,c...whatever...I usually use 1 or 2 letters that remind me of the table I'm working with.

What I also do is alias everything, so in that manner it becomes self documenting....

But if Mr. Lindman shows up, we will have a discussion about fully qualifying with the table name....which you can't even do in DB2 unless you alias it as the name

Like

SELECT * FROM myTable99 myTable99

7/31/2006 10:46 AM | Brett
Gravatar

# re: Alias Naming Conventions

I agree with Mladen. I always use short aliases that match the table name: customers = cu, orders = o, order line items = ol, and so on.

There is NO good reason to use non-semantic aliases, even when you're testing. What good does that do? Remind you of how many tables you're forgetting about?

I say, fight the company's bizarre convention. Otherwise, a few years down the line, they'll want you to alias tables by number: t01, t02, t03, ad nauseum.
7/31/2006 11:22 AM | David Ostroske
Gravatar

# re: Alias Naming Conventions

David/Mladen --

How do you alias derived tables?

- Jeff
7/31/2006 1:55 PM | Jeff
Gravatar

# re: Alias Naming Conventions

well for derived tables i use d___.
where ___ is some very bries vowel-less description
If i can't think of anything smart i just say d1 :)

But i don't like do have that many derived table that i can briefly describe.

My personal rule is if its longer than 5 chars it's not alias :)

all this is pre SQL Prompt era... now i tend to expand to 7 chars for derived ones.
7/31/2006 2:05 PM | Mladen
Gravatar

# re: Alias Naming Conventions

But i don't like do have that many derived table that i can briefly describe.

should be

But i don't like do have that many derived tables that i can't briefly describe.
7/31/2006 2:06 PM | Mladen
Gravatar

# re: Alias Naming Conventions

I'm the person who asked the question.

Thanks Jeff. I'll be sure to get my coworker to read this and hopefully comment on this post as well, since he's a big proponent for non-semantic aliases. He was talking about writing a blog entry about how semantic aliases are bad, so hopefully I can get him to explicate his reasons here.

Thanks again.
7/31/2006 2:11 PM | Chris
Gravatar

# re: Alias Naming Conventions

Chris, best of luck to you!

Jeff, for derived tables, I'll usually use numbers if the derived table is predominantly based on a given table. Say, if I derive something based on a table aliased "c0," then I'll use "c1" to alias the derivation.

It's true that I'm numbering aliases here, but that "c" at the beginning of the aliases' names still means something. And the higher number means I'm dealing with a derivative, not the original. It makes sense that way.
8/1/2006 1:15 PM | David Ostroske
Gravatar

# re: Alias Naming Conventions

Tables should always be aliased with meaningful "names". In my opinion, 2 characters is the absolute minimum, 3 is desirable, 4 is OK, and more if you really need to make things clear (such as a correlated subquery). Numbers should generally not be included unless the same table is self-joined, as in a Parent/Child relationship.

SELECT ord.OrderID, cust.CustomerID
...

Without the table list you can almost guess the name of the table from the list.

SELECT c.MemberID, p.AccountID,
...

Where do these come from?
8/3/2006 4:58 PM | Kevinl
Gravatar

# re: Alias Naming Conventions

I tend to use short names like:
Customer = c
CustomerContacts = cc
Employee = e
Sales = s

I use the Camel Case upper letters for my names. If I have two C table names, I will expand on it like:
Customer = cust
Company = comp
8/4/2006 2:38 PM | Stupid Programmer
Gravatar

# re: Alias Naming Conventions

The concensus is pretty clear so far ... So, Chris, any word on discussions about this with your coworkers? I am still waiting for a reasonable reason why "semantic aliases are bad".
8/4/2006 3:05 PM | Jeff
Gravatar

# re: Alias Naming Conventions

I also use the first letters of words in the table name when I'm aliasing. I also give them numbers, when I have to self-join twice (or more):

SELECT *
FROM MyTable MT1
JOIN MyTable MT2 ON MT1.MT_Id = MT2.Parent_MT_Id

... or whatever. Sometimes, if I'm feeling like typing more, I'll actually name them something like MT_Parent and MT_Child, but I'm usually too lazy to bother :)

I'm also a bit lazy with derived tables. Lately I've been in the habit of simply calling them "x". (Or "y"/"z" if there are more than one... or "p" if I'm using the PIVOT operator)... I don't think it's a big deal at that point, though. The important thing is properly aliasing the core tables--if you see an X in my code, you'll know it's some kind of derived table.

The A, B, C thing is really a horrible convention. Couple that with bad formatting and a few derived tables (that also use A, B, C internally!) and you'll end up with totally unmaintainable code...
8/12/2006 1:31 PM | Adam Machanic
Gravatar

# re: Alias Naming Conventions

Why would you use aliases at all if you don't even have to.... and even then.. still use the whole damn name of the table and tack on a 1 or something to distinguish it.

You're adding to the work your db needs to do to figure out what the heck you are trying to ask it.. when perfectly suitable aliases are already in the server's namespace... y'know.. the table names..

sheesh. not being able to type fast enough is not a suitable reason for aliasing either.. there are plenty of tools that'll let you 'tab complete' tables, and columns..

And if you're query is sooo long that you think cutting down on a few K of transmission between your client and the db, then you really need to rethink the real bottlenecks in any db application environment.
9/20/2006 8:41 AM | Rediculous
Gravatar

# re: Alias Naming Conventions

Rediculous -- I can only hope you are joking around ... if not, then congratulations on writing one of the most idiotic things I have ever read! You also might enjoy my "top 10 things I have about sql" article if you haven't read it yet, I am sure you will agree with all 10 points and provide some more of your own.
9/20/2006 8:50 AM | Jeff
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET