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.