Using Temporary Tables in Stored Procedures

Temporary tables are commonly used in stored procedures when you need to store intermediate results in a work table for additional or more advanced processing. You need to keep a few things in mind when using temporary tables in stored procedures.

In versions of SQL Server prior to 7.0, if a subprocedure referenced a temporary table created externally, a temporary table with the same name and structure had to exist at the time the stored procedure was created. This is no longer the case now that SQL Server performs deferred name resolution. The existence of the temporary table is not checked until the stored procedure is executed.

Local temporary tables created in a stored procedure are automatically dropped when the stored procedure exits. Global temporary tables created in a stored procedure will still exist after the stored procedure exits until they are explicitly dropped (see Listing 28.22) or the user session in which they were created disconnects from SQL Server.

Listing 28.22 Using Local and Global Temporary Tables in Stored Procedures

 create proc temp_test2 as select * into ##temp from publishers select * into #temp from publishers go exec temp_test2 go select * from ##temp go pub_id pub_name                   city           state country ------ -------------------------- -------------- ----- ----------- 0736   New Moon Books             Boston         MA    USA 0877   Binnet & Hardley           Washington     DC    USA 1389   Algodata Infosystems       Berkeley       CA    USA 1622   Five Lakes Publishing      Chicago        IL    USA 1756   Ramona Publishers          Dallas         TX    USA 9901   GGG&G                      Mnchen        NULL  Germany 9952   Scootney Books             New York       NY    USA 9999   Lucerne Publishing         Paris          NULL  France select * from #temp go Server: Msg 208, Level 16, State 1, Line 1 Invalid object name '#temp'. 

Note what happens if you try to run the stored procedure again:

 exec temp_test2  go Server: Msg 2714, Level 16, State 6, Procedure temp_test2, Line 3 There is already an object named '##temp' in the database. 

TIP

Personally, I don't find much use for global temporary tables in stored procedures. The typical reason for using temporary tables in stored procedures is that you need a work area within the stored procedure only. You normally wouldn't want it sticking around after the procedure finishes. Creating a global temporary table in a stored procedure requires an explicit drop of the table before the procedure exits if you no longer need it. If that's the case, what's the benefit of using a global temporary table? Any subprocedures will be able to see and reference a local temporary table created in the calling procedure, so global temporary tables are not needed in that case.

Only if you need to create and populate a worktable and have it available after the procedure exits should you consider using a global temporary table. However, you would have to remember to explicitly drop it at some point before attempting to run the procedure again. However, if an error occurs that aborts processing of the stored procedure, the explicit drop might not be executed.

You might want to include a check for the global temporary table in your stored procedure and drop it automatically before attempting to create it again, as in the following code snippet:

[View full width]

 
 [View full width] 
 create proc myproc  as if exists (select 1 from tempdb..sysobjects where name =  '##global_temp'                                              and type = 'U')     drop table ##global_Temp select * into  ##global_temp from ... 

Temporary Table Performance Tips

All users within SQL Server share the same tempdb database for work tables and temporary tables, regardless of the database in which they are working. This makes tempdb a potential bottleneck in any multiuser system. The primary bottleneck in tempdb is disk I/O, but locking contention can also exist between processes on the tempdb system tables.

SQL Server 2000 solves the disk I/O problem a bit by logging just enough information to allow rollback of transactions without logging all the additional information that would be necessary to recover those transactions. The recovery information is needed only when recovering a database at system startup or when restoring from a backup. Because tempdb is rebuilt during SQL Server startup (and no one in their right mind would restore tempdb from a backup), it's unnecessary to keep this recovery information. By reducing the logging in tempdb, data modification operations on tables in tempdb can be up to four times faster than the same operations in other databases.

On the other hand, locking in tempdb is still a potential performance bottleneck. If you create a table in tempdb within a transaction, locks are held on rows in the system tables sysobjects , syscolumns , and sysindexes related to the table created, as shown in Listing 28.23.

Listing 28.23 Locks Held on System Tables in tempdb When Creating a Temporary Table

 begin tran select * into #temptab from titles exec sp_lock go spid   dbid   ObjId       IndId  Type Resource         Mode     Status ------ ------ ----------- ------ ---- ---------------- -------- ------     51      2           0      0 DB   [BULK-OP-LOG]    NULL     GRANT     51      5           0      0 DB                    S        GRANT     51      2           0      0 DB   [BULK-OP-DB]     NULL     GRANT     51      2           1      0 TAB                   IX       GRANT     51      2           3      0 TAB                   IX       GRANT     51      2           2      0 TAB                   IX       GRANT     51      2           3      2 KEY  (bd018d280de1)   X        GRANT     51      2           3      2 KEY  (b101e50ba351)   X        GRANT     51      2           3      1 KEY  (080056b1859e)   X        GRANT     51      2           3      1 KEY  (0900b81e308c)   X        GRANT     51      2           3      1 KEY  (0e008aeeeebb)   X        GRANT     51      2           3      1 KEY  (0b0064415ba9)   X        GRANT     51      2           3      1 KEY  (0f00dcfe8de3)   X        GRANT     51      2           0      0 PAG  1:78             X        GRANT     51      2           0      0 PAG  1:77             X        GRANT     51      2           0      0 PAG  1:94             X        GRANT     51      2           0      0 PAG  1:95             X        GRANT     51      2           0      0 PAG  1:92             X        GRANT     51      2           0      0 PAG  1:93             X        GRANT     51      2           0      0 PAG  1:90             X        GRANT     51      2           0      0 PAG  1:91             X        GRANT     51      2           0      0 PAG  1:89             X        GRANT     51      2           0      0 EXT  1:104            X        GRANT     51      2           0      0 PAG  1:102            X        GRANT     51      2           0      0 PAG  1:103            X        GRANT     51      2           0      0 PAG  1:100            X        GRANT     51      2           0      0 PAG  1:101            X        GRANT     51      2           0      0 PAG  1:98             X        GRANT     51      2           0      0 PAG  1:99             X        GRANT     51      2           0      0 PAG  1:96             X        GRANT     51      2           0      0 PAG  1:97             X        GRANT     51      2           0      0 PAG  1:110            X        GRANT     51      2           0      0 EXT  1:96             X        GRANT     51      2           0      0 PAG  1:111            X        GRANT     51      2           0      0 PAG  1:108            X        GRANT     51      2           0      0 PAG  1:109            X        GRANT     51      2           0      0 PAG  1:106            X        GRANT     51      2           0      0 PAG  1:107            X        GRANT     51      2           0      0 PAG  1:104            X        GRANT     51      2           0      0 PAG  1:105            X        GRANT     51      2           0      0 PAG  1:118            X        GRANT     51      2           0      0 PAG  1:119            X        GRANT     51      2           0      0 PAG  1:116            X        GRANT     51      2           0      0 PAG  1:117            X        GRANT     51      2           0      0 PAG  1:114            X        GRANT     51      2           0      0 PAG  1:115            X        GRANT     51      2           0      0 PAG  1:112            X        GRANT     51      2           0      0 PAG  1:113            X        GRANT     51      2           0      0 EXT  1:112            X        GRANT     51      2           1      2 KEY  (8516877f1c72)   X        GRANT     51      2           0      0 IDX  IDX: 2:469576711 X        GRANT     51      2   469576711      0 TAB                   Sch-M    GRANT     51      2           3      2 KEY  (54016b2ccfff)   X        GRANT     51      2           1      3 KEY  (0700b7f12a1d)   X        GRANT     51      2           3      2 KEY  (4201380b1b46)   X        GRANT     51      1    85575343      0 TAB                   IS       GRANT     51      2           3      2 KEY  (5c013a008346)   X        GRANT     51      2           3      2 KEY  (9f01b84495cc)   X        GRANT     51      2           3      1 KEY  (110057368449)   X        GRANT     51      2           3      1 KEY  (1000b999315b)   X        GRANT     51      2           3      2 KEY  (eb002ad7df7e)   X        GRANT     51      2           3      1 KEY  (0a00dd798c34)   X        GRANT     51      2           3      1 KEY  (0d00ef895203)   X        GRANT     51      2           3      2 KEY  (36027a4d788e)   X        GRANT     51      2           3      1 KEY  (0c000126e711)   X        GRANT     51      2           1      1 KEY  (0700c2f14c59)   X        GRANT     51      2           2      1 KEY  (07000a2e7633)   X        GRANT     51      2           3      2 KEY  (450124fb6f35)   X        GRANT     51      2           3      2 KEY  (d7016cf8d9e5)   X        GRANT     52      5           0      0 DB                    S        GRANT     53      5           0      0 DB                    S        GRANT 

The tempdb database has a dbid of 2, and the sysobjects table has an object ID of 1; sysindexes is object ID 2, and syscolumns is object ID 3. These locks being held on the system tables could lead to locking contention with other processes trying to read or update the tempdb system catalogs.

To minimize the potential for locking contention on the system tables in tempdb , consider creating your temp tables before starting the transaction so that locks are released immediately and not held on the system catalogs until the end of the transaction. If the table must be created in a transaction, commit your transaction as soon as possible.

Also, be aware that even if it's not in a transaction, creating a temporary table using SELECT INTO will hold locks on the system catalogs in tempdb until the SELECT INTO completes. If locking contention in tempdb becomes a problem, consider replacing SELECT INTO with CREATE TABLE followed by an INSERT using a SELECT statement. Although this might run a bit more slowly than SELECT INTO , the system table locks are held only for the brief moment it takes for CREATE TABLE to complete.

Another way to speed up temp table creation/population is to keep temporary tables as small as possible so they create more quickly. Select only the required columns , rather than SELECT * , and only retrieve the rows that you need. The smaller the temporary table, the faster it will be to create the table as well as to access the table.

If the temp table is of sufficient size and is going to be accessed multiple times within a stored procedure, it might be cost effective to create an index on it on the column(s) that will be referenced in the search arguments of queries against the temp table. The deciding factor of whether to create an index on a temporary table is if the time it takes to create the index plus the time the queries take to run using the index is less than the sum total of the time it takes the queries against the temporary table to run without the index.

The following example demonstrates the creation of an index on a temporary table:

 use bigpubs2000  go create proc p1 WITH RECOMPILE as select title_id, type, pub_id, ytd_sales    into #temp_titles    from titles create index tmp on #temp_titles(pub_id) select sum(ytd_sales)    from #temp_titles    where pub_id = '0736' select min(ytd_sales)    from #temp_titles    where pub_id = '0736' return go 

Some other final tips when using temporary tables in stored procedures:

• Don't use temp tables to combine resultsets together when a UNION or UNION ALL will suffice. UNION ALL will be the fastest because no work table in tempdb is required to merge the resultsets.

• Drop temporary tables as soon as possible to free up space in tempdb .

• Consider using the table datatype to avoid tempdb usage altogether.