Professional SQL Server™ 2005 Programming

23.7. Uses for Temporary Tables

The use of temporary tables can sometimes help performance — usually by allowing the elimination of cursors.

As we've seen before, cursors can be the very bane of our existence. Using temporary tables, we can sometimes eliminate the cursor by processing the operation as a series of two or more set operations. An initial query creates a working data set. Then another process comes along and operates on that working data.

We can actually make use of the pricing example we laid out in the last section to illustrate the temporary table concept, too. This solution wouldn't be quite as good as the correlated subquery, but it is still quite workable and much faster than the cursor option. The steps would look something like:

SELECT ProductID, FLOOR(UnitCost * 1.1) + .49 AS TempUnitPrice
     INTO #WorkingData
     FROM Products
     WHERE (UnitCost * 1.1) - FLOOR(UnitCost * 1.1) < .50
INSERT INTO #WorkingData
SELECT ProductID, FLOOR(UnitCost * 1.1) + .95 AS TempUnitPrice
     FROM Products
     WHERE (UnitCost * 1.1) - FLOOR(UnitCost * 1.1) >= .50
UPDATE p
     SET p.UnitPrice = t.TempUnitPrice
     FROM Product p
     JOIN #WorkingData t
       ON p.ProductID = t.ProductID

With this, we wind up with three steps instead of thirty or forty. This won't operate quite as fast as the correlated subquery would, but it still positively screams in comparison to the cursor solution.

Keep this little interim step using temporary tables in mind when you run into complex problems that you think are going to require ...