I've been using temporary tables in SQL Server for years, and they have helped me solve a lot of problems. Please don't tell Joe Celko about this, as he will have me excommunicated as a SQL developer for my heresey (a quote from J.C. - "...In 20 years, I have found that it is always possible to come up with a subquery, derived table expression or a VIEW instead of a temp table.")

One thing I have noticed, though, is that using temporary tables inside of a transaction can be tricky - I often experience very poor performance or even have the whole process come to a halt. These are often cases where the same procedure *without* the transaction will run very quickly.

The problem seems to be because a much larger number of locks are created in the case of temp tables inside a transaction. In many cases, I've solved the problem by replacing the temp table with a derived table.

I haven't done any real work with SQL Server 2005 yet, but it looks to me as if I can use Common Table Expressions (CTE) in many cases where I would have otherwise use a temporary table.

Thanks for the great article, Randy.

Eric - I like your icon - the first computer I owned was a TI-99/A in 1982!

Best regards,

SteveR