SQL Server 2005 introduced quite a few enhancements related to caching of temporary objects. You can find details about those and other enhancements related to tempdb in the paper: Working with tempdb in SQL Server 2005, which I strongly recommend reading.
One of the enhancements that I find particularly interesting is that SQL Server may cache temporary objects (e.g., temporary tables, table variables) across invocations of a routine (e.g., stored procedure, trigger, UDF). It can keep the metadata representing the temporary object, as well as a couple of pages (data page and IAM page) in cache, preventing the need to allocate and deallocate those every time you execute the routine. For routines that are invoked very frequently, this can save time and reduce contention on system catalog tables and allocation pages.
You need to be aware though that in certain situations SQL Server won’t cache temporary objects. One such case is when you apply DDL to a temporary table after it was created. This one is similar to a cause for a recompile and seems quite reasonable. Another case is when you use named constraints as part of the definition of the temporary object. I find this case rather peculiar, but tests do prove that it’s true.
Next, I’ll demonstrate the caching behavior of temporary objects and situations that prevent such caching.
First, run the following code to create a procedure called TestCaching that creates a temporary table, populates it with a few rows, and then queries it:
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.TestCaching', 'P') IS NOT NULL
DROP PROC dbo.TestCaching;
GO
CREATE PROC dbo.TestCaching
AS
CREATE TABLE #T1(n INT, filler CHAR(2000));
INSERT INTO #T1 VALUES
(1, 'a'),
(2, 'a'),
(3, 'a');
SELECT n, filler FROM #T1;
GO
Before executing the stored procedure, run the following code to see whether you currently have any temporary objects in tempdb:
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
You should get an empty set as output, unless there are temporary objects unrelated to our procedure.
Execute the procedure:
EXEC dbo.TestCaching;
Look for temporary objects in tempdb:
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
Even though the procedure already terminated, the aforementioned information for the temporary table was kept, and subsequent invocations of the procedure can reuse it. You should get an entry in the output for the temporary table that was created by this procedure. I got a table called #117F9D94.
Before the next test, mark the proc for recompile by running the following code:
EXEC sp_recompile 'dbo.TestCaching';
Look again for temporary objects in tempdb:
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
You should get an empty set back.
The next example will demonstrate that DDL applied to the temporary object prevents caching it. Run the following code to alter the procedure, creating an index on the table after creating the table itself:
ALTER PROC dbo.TestCaching
AS
CREATE