Here are many different thoughts/facts/preferences on aliasing.  Most of this applies to column aliasing.  Regardless of whether or not you like aliasing tables, you should still be aliasing columns.  If you do happen to be one of the people who is against table aliasing (some of you may be wondering "Who are these mythical people against table aliasing?", but trust me, they exist), keep in mind that three and four part names in the select clause is on the list of unsupported features in a future version of SQL Server as of SQL 2008.

Aaron Bertrand's ( Blog | Twitter ) recent alias posts

Inconsistent Table Aliasing: Aliasing only some of the tables in a query or aliasing all of the tables and only some of the columns.

Meaningless Alias Names: Using meaningless alias names like A,B,C or T1,T2,T3.  (1 letter aliases are fine, this refers to ones that have nothing to do with the table.)

Problem caused by Inner Queries: A specific problem that can bite you when you’re not paying attention to alias names on inner queries.

The third one of these in particular saved me some time recently, as I had just read that post a couple weeks before, and although I was familiar with the concept, having just read his post about it pushed it to the front of my memory when a query I was writing didn’t make sense.  The second one I disagree slightly with.  I completely agree that you shouldn’t use meaningless alias names, but in the post he mentions that he doesn’t like using any alias names over 3 letters long.  Here I have a different stance, mainly because I like to:

Maintain table aliases across code in a database

I like to maintain alias names across all my queries (wherever possible) so that I know at a glance what tables I'm pulling from without having to read through the entire query.  It makes it easier for me to read my own code and lets me spot things that would normally be a little bit harder to see.  I'd definitely advise this practice.  If you use Redgate SQL Prompt, you can use the built in alias overrides to make it use the aliases you want.   Obviously, this only applies to tables that are frequently used.  If you have 3000 tables in your database, it’s doubtful you’re going to have preset aliases for all of them. 

In reality, I do have quite a few 1 and 2 letter alias names for my most commonly used tables, these are just examples.  I’d love to have all 1-3 letter aliases, but sometimes it’s easier for me to remember/use a slightly longer or more descriptive alias.  At a certain point, depending on the number of tables and such, 1-3 letter aliases can become just about as ambiguous and meaningless as T1.  My most common aliases are usually 2-5 letters with some oddballs going as far as 8 or so characters.

Aliasing Column Names within a Table Alias

Brad Schulz ( Blog | Twitter ) wrote a post a short while ago focusing on UNPIVOT.  What does that have to do with aliasing?  Not much.  His post wasn't about aliasing at all, but it did offhandedly introduce me to something I'd never seen before.  He actually redefines COLUMN names in a table alias.  I had never seen this before.  While I haven't really used it much yet; I still think it's incredibly cool and figured I'd point it out.  You can basically do this:

SELECT TOP 10 O.TableName, I.IndexName
FROM (SELECT do.NAME, do.id FROM sysobjects do) O(TableName, TableID)
    INNER JOIN (SELECT di.NAME, di.id FROM sysindexes di) I(IndexName, TableID) ON O.TableID = I.TableID

While this might seem pointless to some (I could just as easily have created a CTE, aliased the columns within the derived tables or just renamed them in the select clause), I think it might be really useful in some cases.  Maybe it's just because I'm still working with SQL 2000 on a daily basis and I constantly need to deal with nested derived tables.  Either way, now you know (or perhaps you already did, I might be the only person on the planet who didn't know you could do this).

Columns Added to a Table Down the Road

I'm currently in the middle of implementing a large scale update to our primary ERP system.  The main tables are all controlled by a third party vendor.  We also write a ton of custom code over the top of these tables for external reporting and websites and such.  During this upgrade, I've noticed columns added to tables that they were not previously in.  For the most part this wouldn't pose a problem, but in some cases, these additional columns could break queries due to ambiguity errors.  This is even more likely with more 'general' column names... such as 'ref_num'.  This could happen at any time and will break queries if you did not alias the existing ref_num column (even though it was the only one there at the time).

Intellisense Filtering

Even if you don't currently use the built in Intellisense in SQL, chances are that you will at some point in the future.  I've found that using aliases tends to make it much less annoying.  I say less annoying because it still bugs the crap out of me from time to time, but aliasing columns makes it less dumb.

ORDER BY and Ambiguous Column Names

The order by clause may demonstrate a slightly different variation of column name ambiguity breaking queries (depending on SQL version from what I can tell).  Due to the order that queries are processed internally, you can refer to columns in the select list by alias in the order by clause.  Observe the following example.

Removing I.name from the Select list will make the error disappear, even though both sysobjects and sysindexes have a *name* column.

As you can imagine, this could lead to confusion on more complex queries and waste some troubleshooting time.  It's also good to be aware of the way the order by is working in case you do something like this:

(Note that this example is from a 2K8 database, the same type of query (that doesn't use a DMV) fails in 2K for all the above examples.)

Group fields in the select list by the table they come from

This is just personal preference and not something that is likely to have any impact other than readability, but I find it makes queries a lot easier to deal with and to understand.  It also makes it easy to comment out all references to a table when you are troubleshooting queries.  I actually go so far as to add a comment above the group of fields from a particular table in really large queries, but again, purely personal preference.

In closing, a short totally unbiased checklist of some of the pros and cons of aliasing. 

(And depending on whether or not you use some sort of Intellisense software, the ‘Against’ might not even be true!)