Are SQL Server Temp Tables Really Necessary?

New Query
(I usually name my temporary table after the stored procedure that created it so I can more quickly troubleshoot any problems in tempdb from the use of temporary tables.)

CREATE TABLE #tquery2a
(multiplecolumns DATATYPES)

CREATE TABLE #tquery2b
(mulitplecolumns DATATYPES)

INSERT INTO #tquery2a
SELECT columns FROM dbo.table2 WHERE table2.strPhoneNumber = '5555555555'

INSERT INTO #tquery2b
SELECT columns FROM dbo.table3 WHERE table3.strPhoneNumberCalled = '1234561234'

SELECT table1.numCustID, #tquery2a.strPhoneNumber, #tquery2b.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN #tquery2a #tquery2a
ON table1.numBillID = #tquery2a.numBillID
INNER JOIN #tquery2b #tquery2b
ON #tquery2a.numBillDtlID = #tquery2b.numBillDtlID
WHERE table1.numCustID = '5555'
ORDER BY #tquery2b.dtmCalled DESC

Believe it or not this method works, especially with the ORDER BY statement, and its performance is vastly better than the original query.

Reporting off an OLTP designed databases is not always the easiest thing to do. The database is just built to maximize reports that executives want. Using temporary tables to stage the results from numerous SELECT statements, aggregating those results before displaying them is sometimes the only way to can get reports out of an OLTP database.

Working in a call center application you are usually asked to produce reports that summarize what the call center reps are doing on a time filtered basis. Working your way through all the tables to gather the data and then summarizing it in multiple ways can only be accomplished with the use of temporary tables.

Before any comes up with this argument: I know I work in a multi-billion company, but that doesn't mean that executives are willing to listen to our arguments that they need a data warehouse or a simple reporting database if it means they have to spend money to get one when they can just as easy piggy-back off of the OLTP database and blame me if the queries are too slow and cause a performance headache for the servers. Sorry, that was for the theoretical guys out there who have magically gotten everything they wanted no matter the cost or the size of the companies they worked for.

The last argument for the use of a temporary table is to replace a cursor. I am not fond of cursors and advocate doing anything possible to replace the cursor (performance of your solution needs to be tested against the performance of the cursor though). One of the tricks I use is to mimic the main reason a cursor is usually built for, looping through a result set one row at a time and performing an action based on the data in that row. Below is a short query that displays this logic by obtaining all the user table names and executing sp_spaceused on each table.

SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME

CREATE TABLE #tTables
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)

INSERT INTO #tTables (strTableName)
SELECT name FROM dbo.sysobjects WHERE xtype = 'u'

SET @lngTabCount = @@ROWCOUNT
SET @lngLoopCount = @lngTabCount

WHILE @lngLoopCount <> 0
BEGIN
SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)
EXEC sp_spaceused @strTabName
SET @lngLoopCount = @lngLoopCount - 1
END

DROP TABLE #tTables
GO

This example demonstrates cursor-like actions without cursor overhead and performance related problems.