Recently I posted in Twitter (https://twitter.com/RicVanDyke) that
you shouldn’t use single character aliases.
And I made the point that using full table names is best. This of course sparked a good dialog that
went on for about two days.
After tuning hundreds of queries with thousands of lines in each
of them, I really think that full table names are best. Next best is shortening the name by removing
non-leading vowels. And even more important is make aliases unique
within the statement. This means if you
use the same table more than once, put a 01, 02 and so on after each use.
But you say “What if the table name is really long?” Are you
really that lazy? You can’t use copy/paste?
But you say “My query is really short, using single characters
just makes sense.” No, this is about
the habit. You start doing in a small
query, 5-6 lines long. Then you say,
well this one is only 20 lines its OK to use single characters. Then these queries get pushed together, and
before you know it you have a several hundred line query with all these “A” and
“E” and “C” and what not in it. Which “A” goes with which table?
And yes this applies to columns and subqueries as well. Don’t have subquery “A” unioned with subquery
“B”. Give them a name. Heck I’m OK with SUBQ01 and SUBQ02. That is way better to search a huge query for
SUBQ01 then looking for “A”. Trust me,
that is painful.
I’m sticking to my guns on this. Never use single character aliases. Shorter names are fine, but keep it close to
the name of the table. Sure for
EMPLOYEES using EMP is fine, but not E. Because
you’ll find yourself with several subqueries all using “E” and then you’re
looking at the execution plan trying to figure out which sub query this
predicate goes with. And instead of it taking
seconds, it’s hunting expedition to figure it out. I’ve had to do this way too
many times.
Of course all is good when it’s your code; you (presumably)
knew what you were doing when you wrote it.
What about when someone else is reading your code?
Or you are reading it 5 years later? Do you really think you’ll remember what you
were doing 5 years ago? Heck I have trouble
working on code from yesterday.
Help others and yourself, don’t use single character
aliases. Make your code easier to read
and debug by being more verbose then terse.
You’ll thank yourself later.
