Temporary Table Caching Explained

SQL Server (since 2005) caches temporary tables and table variables referenced in stored procedures for reuse, reducing contention on tempdb allocation structures and catalogue tables.

A number of things can prevent this caching (none of which are allowed when working with table variables):

• Named constraints (bad idea anyway, since concurrent executions can cause a name collision)
• DDL after creation (though what is considered DDL is interesting)
• Creation using dynamic SQL
• Table created in a different scope
• Procedure executed using WITH RECOMPILE

Temporary objects are often created and destroyed at a high rate in production systems, so caching can be an important optimization.

Temporary Table Caching in Stored Procedures

Introduction

Ask anyone what the primary advantage of temporary tables over table variables is, and the chances are they will say that temporary tables support statistics and table variables do not.

This is true, of course. The indexes that enforce PRIMARY KEY and UNIQUE constraints on table variables do not have populated statistics associated with them. Neither do any non-constraint table variable indexes (using inline index definitions, available starting with SQL Server 2014). Finally, it is not possible to manually create statistics on table variables.

Intuitively, then, any query that has alternative execution plans to choose from ought to benefit from using a temporary table rather than a table variable. This is also true, up to a point.

Viewing Another Session’s Temporary Table

Is it possible to view the contents of a local temporary table, created on a different connection?

Say we run this code on connection 1:

CREATE TABLE #Private
(
    [data] nvarchar(30) NOT NULL
);
GO
INSERT #Private
(
    [data]
)
VALUES
(
    N'Only I can see this'
);

Is there a way to see the contents of the #Private table from connection 2?

It isn’t particularly easy, but a user with sysadmin permissions, a bit of internal knowledge (and some patience) can do it.