SQL Server Performance and temporary tables

Yesterday I golbed about SQL Server performance and I thought I’d continue on in that vein today.
Temporary tables where a feature in SQL Server that I tended to avoid. I figured that I should handle everything through code and using subqueries (with IN and EXISTS clauses) or even the new table variables (well… new since SQL Server 2000), but there are excellent efficiencies to be gained by using the tempdb database.
There are two types of temporary table: local (prefixed with #) or the global (##). I haven’t found much use for the latter type, but the former is useful when you are doing repeated tasks, in a stored procedure, with the same information. Instead of querying the data each time with IN or EXISTS you can dump it into a temporary table and then join to that table.
Here’s an example using yesterday’s Adventureworks code. Let’s say I’m writing a stored procedure to do a great deal of work on Products which have a Product Number beginning with FW. I could use this:

USE AdventureWorks ;
GO
create table #productids (productid int NOT NULL primary key);

insert into #productids
SELECT ProductID
FROM Production.Product p
WHERE ProductNumber LIKE ‘FW%’
order by ProductID

select
/* or do something else with */
SalesOrderID
FROM Sales.SalesOrderDetail sod, #productids p
WHERE sod.ProductID = p.productid

/* and now do something else with it */

/* but at the end don’t forget to: */
drop table #productids

This can really cut down on your execution time and you also benefit by making your code more modular and readable.

PS: If you copy this code to fool around with it, don’t forget to replace the singles quotes with normal single quotes as you may be getting strange replacement characters over the internet.