Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 227

To use the script yourself simply change the stored procedure name you want to test from usp _ loop _
test _ table to whatever code you want.

If the code you want to test is complicated, you might want to set the loop iterations to 1 the i rst
time you run this script just to be sure how many different temporary tables are created. Once you
know that, you can set it back to 10 loop iterations as in the example.

The example code indicates that only one temporary table creation statement is called many times,
so if the value returned from the above script is more than 1 , then you can be coni dent that you're
not getting temporary object reuse.

Running the script provides the following result:

Temp tables created during the test: 1000

During 10 executions, 1,000 temporary tables were created, so you can conclude that the table isn't
being cached (remember that the looping procedure executes the procedure creating the temp table
100 times, 10 * 100 = 1,000).

There's obviously a problem in the example code somewhere, so what you need to determine now
is under what circumstances SQL Server will cache temporary tables, so you know whether any
changes can be made to the code.

Temporary objects will be cached as long as the following obtains:
➤

Named constraints are not created.

➤

DDL (Data Dei nition Language) statements that affect the table, such as CREATE INDEX or
CREATE STATISTICS , are not run after the table has been created.

➤

The object is not created using dynamic SQL; using sp _ executesql , for example.

➤

The object is created inside another object such as the following:
➤

Stored procedure

➤

Trigger

➤

User-dei ned function

➤

The return table of a user-dei ned table-valued function

If you look back at the code for usp_temp_table you'll notice that a unique clustered index is cre-
ated after the table dei nition, which breaks the rules for cached temporary objects:

CREATE UNIQUE CLUSTERED INDEX cix_c1 ON #tmptable ( c1 ) ;

All is not lost, however, because you can utilize a constraint within the temporary table dei nition to
achieve the same results without breaking the rules for temporary object caching. The next code
listing shows the new dei nition with the old CREATE INDEX statement commented out.

USE [tempdbdemo] ;
GO
CREATE PROCEDURE [dbo].[usp_temp_table]
AS