Nicely written article.

I too have found temporary tables to be invalueable. In fact, I learned the technique by reading the query plans. When I saw SQL Server creating a work table I understood that to be a way of breaking down the work.

There are times when I know better (go figure) than the query optimizer how the data is distributed. In those cases I have found that temporary tables can radically outperform any other method available. Indeed, I have even found that temporary tables can out-perform table variables.

Most of the topics for the links you referenced were for obscure issues with temporary tables. I have not found contention or radically reduced issues since SQL 2k with the creation of temp tables. Especially since MS fixed the SELECT INTO bug. The other issues dealing with Identity columns or BCP into a temp table are rather esoteric.

I say use them if they work...and many times they work well.

Cheers,

Ben Taylor
btaylor@sswug.org

I have some kinds of this experieces.

For your query,

SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN dbo.table2 table2
ON table1.numBillID = table2.numBillID
INNER JOIN dbo.table3 table3
ON table2.numBillDtlID = table3.numBillDtlID
WHERE table1.numCustID = '5555'
AND table2.strPhoneNumber = '5555555555'

AND table3.strPhoneNumberCalled = '1234561234'
ORDER BY table3.dtmCalled DESC

I would rewrite it to:

SELECT t1.numCustID, t2.strPhoneNumber, t3.strPhoneNumberCalled
FROM

(Select * from dbo.table1 WHERE dbo.table1.numCustID = '5555'
) as t1
INNER JOIN

(Select * from dbo.table2 WHERE dbo.table2.strPhoneNumber = '5555555555'
) as  t2
ON t1.numBillID = t2.numBillID
INNER JOIN

(Select * from dbo.table3 WHERE dbo.table3.strPhoneNumberCalled = '1234561234') as t3
ON t2.numBillDtlID = t3.numBillDtlID
ORDER BY table3.dtmCalled DESC

So, no temp table needed but performance is ok.

"Table Variables are more performant than temp tables because they don't use tempdb."

From my experience I have seen table variables still built into tempdb.  Do some simple testing and you will see the same results.  You are accurate as Table variables do seem to perform better than classic table vars.

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

agree with pokitlok I always do the same. but some times I use temp table , must of the time to avoid cursors.