SQL Server: temporary table vs table variable
I had written this article initially in 2010 for SQL Server 2008 R2 but updated it recently using SQL Server 2022 (16.0).
Let's take a look on the differences between temporary tables (#table_name) and table variables (@table_name) in SQL Server.
Memory/disc storage
Temporary tables are stored in the system tempdb database whereas a table variable starts from memory allocation.
However, when a table variable reach the size of an extent (8 pages = 64 KB), it starts allocate a disc space in tempdb, too.
A table variable isn't a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data.
SET NOCOUNT ON;
USE tempdb;
DECLARE @v TABLE (name nvarchar(4000)); -- about 1 page per line to store
DECLARE @results TABLE(
lines_in_table_variable int,
unallocated_extent_page_count int,
user_object_reserved_page_count int,
total_page_count int
);
DECLARE @i int = 0;
WHILE @i < 100 BEGIN
IF @i % 10 = 0 OR @i BETWEEN 1 AND 9 OR @i = 99
INSERT INTO @results
SELECT
@i AS lines_in_table_variable,
SUM(unallocated_extent_page_count) AS unallocated_extent_page_count,
SUM(user_object_reserved_page_count) AS user_object_reserved_page_count,
SUM(total_page_count) AS total_page_count
FROM sys.dm_db_file_space_usage;
INSERT INTO @v (name)
SELECT replicate(N'V', 4000);
SET @i = @i + 1;
END;
SELECT * FROM @results;
Result shows that the very first row start to allocate tempdb space, and continue to allocate new space every 8 rows.
Comparing with SQL Server 2022, the 2008 R2 did not allocate the initial space, so any small enough table variable was really memory stored.
lines_in_table_variable unallocated_extent_page_count user_object_reserved_page_count total_page_count
----------------------- ----------------------------- ------------------------------- ----------------
0 7672 224 8192
^^^ initial tempdb state
1 7648 240 8192
^^^ initial allocation
2 7648 240 8192
3 7648 240 8192
4 7648 240 8192
5 7648 240 8192
6 7648 240 8192
7 7648 240 8192
8 7648 240 8192
9 7640 248 8192
^^^ first additional allocation
10 7640 248 8192
20 7624 256 8192
30 7616 264 8192
40 7608 272 8192
50 7584 288 8192
60 7576 296 8192
70 7568 304 8192
80 7552 312 8192
90 7536 328 8192
99 7528 336 8192
Metadata
When you declare a table variable or a temporary table, the metadata is also created in the corresponding system tables.
USE tempdb;
DECLARE @v TABLE (
id int,
name nvarchar(100)
);
CREATE TABLE #t (
id int NOT NULL PRIMARY KEY,
name nvarchar(100)
);
SELECT
OBJECT_NAME(c.object_id) AS object_name,
c.object_id,
c.name
FROM
sys.columns c
WHERE
c.object_id < 0 -- for 2008-2012 versions: c.object_id > 127
AND c.name IN ('id', 'name')
;
DROP TABLE #t;
Results
object_name object_id name
--------------------------------- ----------- ----------
#B5BA5E1A -1246077414 id
#B5BA5E1A -1246077414 name
#t____________________000000000024 -1230077357 id
#t____________________000000000024 -1230077357 name
Transactions
Temporary tables are in the scope of an explicit transaction whereas table variables are out of scope.
SET NOCOUNT ON;
CREATE TABLE #t (id int);
DECLARE @v TABLE (id int);
INSERT INTO #t VALUES(1);
INSERT INTO @v VALUES(1);
BEGIN TRANSACTION
INSERT INTO #t VALUES(2);
INSERT INTO @v VALUES(2);
ROLLBACK
SELECT id AS "id #t" FROM #t;
SELECT id AS "id @v" FROM @v;
DROP TABLE #t;
Results
id #t
----------
1
id @v
----------
1
2
Indexation
You may create indexes on temporary tables but not on table variables. However you may declare primary key or unique constraints for both of them.
CREATE TABLE #t (id int);
DECLARE @v TABLE (id int);
CREATE INDEX IX1_TT ON #t(id);
CREATE INDEX IX2_TT ON @v(id);
DROP TABLE #t;
The temporary table is created but declaring the variable raises the error
Msg 102, Level 15, State 1, Line 7 Incorrect syntax near '@v'
The following variable declaration is correct
DECLARE @v TABLE (id int PRIMARY KEY, name nvarchar(100) UNIQUE);
Visibility. Local and global temporary tables
A table variable is visible in the current scope only (script or stored procedure) but you may pass a table as a parameter with READONLY option ; user datatype declaration is required.
A local temporary table (prefixed by single #) exists from the CREATE TABLE, and all the execution time or until an explicit DROP TABLE statement.
The table is visible for all stored procedures called from the procedure or script that has created it.
A global temporary table (prefixed with ##) is visible by all other users.
CREATE TYPE my_table_type AS TABLE ( id int)
GO
CREATE PROCEDURE my_proc1
AS BEGIN
SET NOCOUNT ON;
DECLARE @v my_table_type;
CREATE TABLE #t (id int);
INSERT INTO @v (id) VALUES (1);
INSERT INTO #t (id) VALUES (2);
EXEC my_proc2;
EXEC my_proc3 @v;
END
GO
CREATE PROCEDURE my_proc2
AS BEGIN
SET NOCOUNT ON;
SELECT id AS "id #t" FROM #t;
END
GO
CREATE PROCEDURE my_proc3
@v my_table_type READONLY
AS BEGIN
SET NOCOUNT ON;
SELECT id AS "id @v" FROM @v;
END
GO
EXEC my_proc1
GO
DROP PROCEDURE my_proc1
DROP PROCEDURE my_proc2
DROP PROCEDURE my_proc3
GO
DROP TYPE my_table_type
GO
Results
id #t
-----------
2
id @v
-----------
1
Performance
Data modifications
As already mentioned above, table variables are out of transaction scope, so insertion, modification or removing data will be faster than for temporary tables.
However, queries that modify table variables don't generate parallel query execution plans,
so any massive UPDATE or DELETE statement may be slower.
The following example does not optimize table insert operations using explicit transaction batches by 100-10000 rows or the
DELAYED_DURABILITYdatabase setting. We compare two cases "as is" by default.
CREATE TABLE #results (
source sysname,
lines_inserted int,
elapsed_time_msec int
);
GO
CREATE PROCEDURE test_tmp_table
@insert_count int
AS BEGIN
DECLARE @started_at datetime = getdate();
CREATE TABLE #t (
id int PRIMARY KEY,
str_value nvarchar(100)
);
DECLARE @i int = 1;
WHILE @i <= @insert_count BEGIN
INSERT INTO #t (id, str_value)
VALUES(@i, convert(nvarchar(100), rand() * @i));
SET @i = @i + 1;
END
INSERT INTO #results
VALUES(object_name(@@procid), @insert_count, datediff(ms, @started_at, getdate()));
END
GO
CREATE PROCEDURE test_table_var
@insert_count int
AS BEGIN
DECLARE @started_at datetime = getdate();
DECLARE @v TABLE (
id int PRIMARY KEY,
str_value nvarchar(100)
);
DECLARE @i int = 1;
WHILE @i <= @insert_count BEGIN
INSERT INTO @v (id, str_value)
VALUES(@i, convert(nvarchar(100), rand() * @i));
SET @i = @i + 1;
END
INSERT INTO #results
VALUES(object_name(@@procid), @insert_count, datediff(ms, @started_at, getdate()));
END
GO
DBCC DROPCLEANBUFFERS
GO
EXEC test_tmp_table 100
EXEC test_table_var 100
EXEC test_tmp_table 10000
EXEC test_table_var 10000
EXEC test_tmp_table 1000000
EXEC test_table_var 1000000
GO
SELECT * FROM #results;
DROP PROCEDURE test_tmp_table
DROP PROCEDURE test_table_var
DROP TABLE #results;
GO
Results
source lines_inserted elapsed_time_msec
-------------- -------------- -----------------
test_tmp_table 100 7
test_table_var 100 0
test_tmp_table 10000 70
test_table_var 10000 44
test_tmp_table 1000000 5090
test_table_var 1000000 4467
Data selection
Large table variables can affect query performance:
- indexes can't be created explicitly ;
- aren't supported in the SQL Server optimizer's cost-based reasoning model ;
- don't have distribution statistics
- CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions
Use temporary tables when you plan store and query large data volumes.
Forced recompiling
Old SQL Server versions may recompile stored procedure containing temporary tables every time we call them. Starting from 2005 version this problem has gone in the majority of cases.
USE AdventureWorks;
SET NOCOUNT ON;
GO
CREATE PROCEDURE dbo.test_proc1
@last_name nvarchar(100),
@divider int
AS BEGIN
SET NOCOUNT ON;
CREATE TABLE #t (
id int,
name nvarchar(100),
name_len int
);
INSERT INTO #t (id, name, name_len)
SELECT C.ContactID, C.LastName, 0
FROM Person.Contact C
WHERE C.LastName LIKE @last_name
;
UPDATE #t
SET name_len = len(name)
WHERE id % @divider <> 0;
EXEC dbo.test_proc2;
END
GO
CREATE PROCEDURE dbo.test_proc2
AS BEGIN
SELECT COUNT(*) AS line_count
FROM #t
WHERE name_len > 0;
END
GO
SELECT object_name(objid) AS procedure_name, usecounts
FROM sys.syscacheobjects
WHERE objid IN (object_id('dbo.test_proc1'), object_id('dbo.test_proc2'))
;
EXEC dbo.test_proc1 'Ada%', 2;
SELECT object_name(objid) AS procedure_name, usecounts
FROM sys.syscacheobjects
WHERE objid IN (object_id('dbo.test_proc1'), object_id('dbo.test_proc2'))
;
EXEC dbo.test_proc1 'Smi%', 4;
SELECT object_name(objid) AS procedure_name, usecounts
FROM sys.syscacheobjects
WHERE objid IN (object_id('dbo.test_proc1'), object_id('dbo.test_proc2'))
;
GO
DROP PROCEDURE dbo.test_proc1;
DROP PROCEDURE dbo.test_proc2;
GO
Results
procedure_name usecounts
--------------- -----------
line_count
-----------
43
procedure_name usecounts
--------------- -----------
TestP1 1
TestP2 1
line_count
-----------
80
procedure_name usecounts
--------------- -----------
TestP1 2
TestP2 2
Conclusions
A table variable may be a good choice to process small data volumes (10-1K rows or 1 extent) when a selection by complex criteria is not required.
Pass a table variable as a read-only parameter is more explicit and clear way than reusing a declared temporary table in calls of multiple stored procedures.
Prefer to use temporary tables in the other cases.