gotchasqlCritical

What's the difference between a temp table and table variable in SQL Server?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thewhatserversqltempdifferencebetweenandvariabletable

Problem

This seems to be an area with quite a few myths and conflicting views.

So what is the difference between a table variable and a local temporary table in SQL Server?

Solution

Contents

Caveat

This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details).

Storage Location

No difference. Both are stored in tempdb.

I've seen it suggested that for table variables this is not always the case but this can be verified from the below

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Example results (showing location in tempdb the 2 rows are stored)

File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Logical Location

Table variables behave more as though they were part of the current database than #temp tables do. For table variables (since 2005) column collations if not specified explicitly will be that of the current database whereas for #temp tables it will use the default collation of tempdb (More details).

User-defined data types and XML collections must be in tempdb to use for #temp tables and table variables (Source). User-defined alias types must exist in tempdb for #temp tables, but table variables use the context database.

SQL Server 2012 introduces contained databases. the behavior of temporary tables in these differs (h/t Aaron)

In a contained database temporary table data is collated in the collation of the contained database.

All metadata associated with temporary tables (for example, table and column names, indexes, and so on) will be in the catalog collation.

Named constraints may not be used in temporary tables.

Temporary tables may not refer to user-defined types, XML schema collections, or user-defined functions.

Visibility to different scopes

Table variables can only be accessed within the batch and scope in which they are declared. #temp tables are accessible within child batches (nested triggers, procedure, exec calls). #temp tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global ##temp tables can be though).

Lifetime

Table variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.

Although the parser will not allow you to try and use the table variable before the DECLARE statement the implicit creation can be seen below.

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends (if created in a child batch with @@NESTLEVEL > 0) or when the session ends otherwise.

NB: Within stored routines both types of object can be cached rather than repeatedly creating and dropping new tables. There are restrictions on when this caching can occur however that are possible to violate for #temp tables but which the restrictions on table variables prevent anyway. The maintenance overhead for cached #temp tables is slightly greater than for table variables as illustrated here.

Object Metadata

This is essentially the same for both types of object. It is stored in the system base tables in tempdb. It is more straightforward to see for a #temp table however as OBJECT_ID('tempdb..#T') can be used to key into the system tables and the internally generated name is more closely correlated with the name defined in the CREATE TABLE statement. For table variables the object_id function does not work and the internal name is entirely system generated with no relationship to the variable name (the name is the hexadecimal form of the object id).

The below demonstrates the metadata is still there however by keying in on a (hopefully unique) column name. For tables without unique column names the object_id can be determined using DBCC PAGE as long as they are not empty.

```
/Declare a table variable with some unusual options./
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B)
WITH (FILLFACTOR = 80,
IGNORE_DUP_KEY = ON,
DATA_COMPRESSION = PAGE,
ALLOW_ROW_LOCKS=ON,
ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
t.name,
p.rows,
a.type_desc,
a.total_pages,
a.used_pages,
a.data_pages,
p.data_compression_desc
FROM

Code Snippets

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B) 
    WITH (FILLFACTOR = 80, 
         IGNORE_DUP_KEY = ON, 
         DATA_COMPRESSION = PAGE, 
         ALLOW_ROW_LOCKS=ON, 
         ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM   tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
         ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
         ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
         ON c.object_id = p.object_id
WHERE  c.name = 'dba.se'

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141), 
       (2, 0x41414141414141414141)

UPDATE @T
SET    B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id 
FROM   sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
         ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
         ON c.object_id = p.object_id
WHERE  ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE  AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
     AS (SELECT Operation,
                Context,
                CASE
                  WHEN AllocUnitId = @allocId THEN 'Table Variable'
                  WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
                  ELSE AllocUnitName
                END AS AllocUnitName,
                [Log Record Length]
         FROM   fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
                     WHEN GROUPING(Operation) = 1 THEN 'Total'
                     ELSE Operation
                   END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM   T
GROUP  BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER  BY GROUPING(Operation),
          AllocUnitName

Context

StackExchange Database Administrators Q#16385, answer score: 725

Revisions (0)

No revisions yet.