Difference between Temp Table and Table Variable

Note: this article is published on 08/01/2024.

This series of articles is about SQL Tables.

• Table(s): Temp Table, Table Variable, and CTE (Common Table Expression)
  • Difference between Temp Table and Table Variable --- This article
• Performance: Temp Table vs. Table Variable vs. CTE
• Table As Input Parameters For Stored Procedure

A. Introduction

In general, temp table is a table, but a temporary one. That means, temp table has the all features a table has, while the time scale and space scale are different from ragular table. Therefore, in this article, we only discuss the time scale (how long to last) and space scale (location) for temp table, and then concetrate on the differences between Temp table and Table Variable, while the next article will discuss the differences between temp table and CTE. The following is the content of this article:

• A - Introduction
• B - Differences between Temporary Table and Table variable
  • Table - Difference between CTE and Temp Table and Table Variable
• C - Setup Test Environment
• D - Testing
  • D.1 - Time Scale
    • Local --- #
      • Running Scope --- #, ##
    • Global --- ##
    • Scoped --- @
  • D.2 - Space Scale
  • D.3 - ALTER
  • D.4 - Input Parameter to Stored Proceure
  • D.5 - Transaction
  • D.6 - Performance
• References

In B, we list the differences, while in C and D, we setup an invironment to test the statements, some are positive, including some negative.

B - Differences between Temporary Table and Table variable

The folling conclusions are from [ref], I make it as starting point to examine or verify the correctnesses. Most of them are correct, some is not.

1. The table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. However, if there is memory pressure the pages belonging to a table variable may be pushed to tempdb. --- this statement might not be true, the true statement should be "both temp table and Table Variable are created in tempdb database", see D.2: Space Scale.
2. Table variables cannot be involved in transactions (see: D.5: Transaction), logging, or locking. This makes @table faster than #temp. So table variable is faster than the temporary table.
3. Temporary tables are allowed ALTER whereas, Table variables aren’t allowed instead they can add Primary Key, Unique Constraint, clustered index, non-clustered indexes from declaration statement (see D.3: Index).
4. A table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables (see D.4: Input Parameter to Stored Procedure).
5. Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine (see D.1: Time Scale).
6. A temporary table allows Schema modifications, unlike Table variables (see D.3: Index, bottom Note) --- this is the same as 3.
7. Tables variables can't be used as the target of the INTO clause in a SELECT ... INTO statement. [ref]

Table - Difference between CTE and Temp Table and Table Variable

This table describes the main differences between Temp Table, Table Variable and CTE [ref]:

Feature	CTE	Temp Table	Table Variable
Definition	Named temporary result set defined within a query using WITH clause	The physical table stored in tempdb	An in-memory structure defined within a batch or procedure
Scope	Current query	Session or global (depending on the type)	Current batch or stored procedure
Persistency	Exists only for the duration of the query	Persistent until explicitly dropped or a session ends	Deleted automatically when the batch or procedure completes
Performance	Can be efficient for complex queries, especially with recursive logic	Often faster for large datasets and multiple operations	Can be very fast for small datasets and simple operations
Features	Improves readability and maintainability, can be reused within a query	Supports indexes, constraints, and statistics	Cannot have indexes or SQL constraints, and no statistics are maintained
Suitability	Hierarchical or recursive queries, improving code readability, complex logic within a single query	Large datasets, multiple operations, frequent use across queries	Small datasets, simple operations within a batch or procedure, passing data between procedures
Additional Notes	Cannot be used in SQL functions, cannot be accessed directly outside the defining query	May impact performance if heavily used or large datasets involved can be affected by transaction rollbacks	Limited data type support, can be limited by tempdb size

 

C - Setup Test Environment:

Stored Procedure:

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[Ten Most Expensive Products_1]    Script Date: 7/31/2024 2:50:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Ten Most Expensive Products] AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC


DECLARE @input TABLE
( CategoryID int, CategoryName nvarchar(50) )
INSERT INTO @input([CategoryID], [CategoryName]) VALUES(8,'SeaFood3')

SELECT * FROM @input

DROP TABLE IF EXISTS #input
SELECT * 
INTO #input
FROM @input

SELECT * FROM #input

In fact, the top part of the script is a native Stored Procedure from Northwind database:

• Ten Most Expensive Products

exec the stored procedure, we have

At the bottom of the script, actually we defined one Temp Table --- #input, and one Table Variable --- @input:

Table Variable first, because when we defined the temp table, we use the result of the Table Variable:

Define Temp Table

Save and Run the whole stored procedure, the result will include the original output, plus two SELECT output from the Table Vatiable @input and the Temp Table #input:

D - Testing

The following testing will examine or verify some conclusion discussed above.

D.1 - Time Scale

As we know, or discussed in the previous article: Table(s): Temp Table, Table Variable, and CTE , 

Temp Table with time scale 

• # --- local temp table, visible in individual session, including the nested sub session

demo by [ref]

• ## --- global temp table, visible for all alive sessions, and drop after all sessions are closed.

demo by [ref]

Table Variable time scale

• in a scope, such in a stored procedure, in a query script

Testing:

Test 1: Run the stored procedure

exec [Ten Most Expensive Products]

then Check the tempdb, the Temporaty Table, we can see some temp table creaed (why two, not sure):

However, when we tried to run the Query to SELECT from @input,

We got error message, that the Table Variable disppeared. Yes, the lifetime for the Table Variable @input is only in  the scope of this stored procedure.

Try #input:

We got the same, that is because although the temp table has a session lifetime, but if it is running within a scoped function, such as stored procedure, then the lifetime will be only in that scope.

After seconds, refresh the Temparory Table, it will be nothing is there --- the temp tables created by the Stored Procedure is gone.

Test 2, Run the Script outside the Stored Procedure

Then check the Temporary Table, we will see a temp table named dbo.#input created:

then SELECT again, we will see the result:

Due to we run the script in a SSMS session, before the session is out, the temp table will stay in Temp Database.

Test 3: Sub Session

We open a new SQL SSMS, after that, close the original SSMS page (session), check the temp database, we can see the #input is still there. However, on the other hand, SELECT from @input, we still have

This means the @input is only exists in the moment when the script is running.

D.2 - Space Scale

As we already discussed, temp table is created in temp database. By Microsoft, the Table Variable is also created in temp database instead of in memory [ref], although I have not got a way to verify it by code:

"A traditional table variable represents a table in the tempdb database. For much faster performance you can memory-optimize your table variable."

Microsoft told us

• Table Variable is created in tempdb database
• We can make it in memory using memory-optimized Table Variable --- in the same article from Microsoft

To make a memory-opmized table variable, we cannot use inline definition or the traditional definition of Table Variable, such as

DECLARE @tvTableD TABLE  
    ( Column1   INT   NOT NULL ,  
      Column2   CHAR(10) );

The inline syntax does not support memory-optimization. Instead, we should convert the inline syntax to the explicit syntax for the TYPE --- this is what we discussed in the article Table As Input Parameters For Stored Procedure (c-sharpcorner.com), as Table Type, or Table-Valued Parameters:

where MEMORY_OPTIMIZED Table variable by adding 

MEMORY_OPTIMIZED = ON

A memory-optimized table variable does not reside in tempdb. Memory-optimization results in speed increases that are often 10 times faster or more.

D.3 - ALTER

Temporary tables are allowed ALTER like the regular table whereas Table variables aren’t allowed ALTER instead they need to get all constrain in the declare statement, incluidng Primary Key, Unique Constraint, clustered index, non-clustered indexes and so on.

The following can be seen: Table Variable can add a Primary Key in its definition, while Temp Table can add Primary Key and Non-Clustered Indexes by ALTER (CREATE after the table is defined):

The following demo: Temp Table can add a Clustered Index (if there is no Primary Key defined), while Table Variable cannot add either Primary Key (it can be defined in its definition), Clustered Index or Non-Clustered Index by ALTER or CREATE after the Table Variable has been declared:

This will show add Unique constrain, clustered index, non-clustered indexes into a Table Variabel declaration:

Declare @MyTable1 Table
(
    a int NOT NULL  UNIQUE
    ,b smallint NOT NULL index IX_indexName clustered
    ,c smallint NOT NULL index IX_indexName1 nonclustered
    ,d smallint NOT NULL index IX_indexName2 nonclustered
    ,e smallint NOT NULL
    ,f smallint NOT NULL
);

Results:

Note: Microsoft idicates: "Table variables can't be altered after creation." [ref]

D.4 - Input Parameters to Stroed Procedure

We used the testing sample from article, Table As Input Parameters For Stored Procedure, where the stored procedure uses an input as Table-Valued parameter (see details from the orginal article, that we need to create the Table-Valued type first):

CREATE PROCEDURE usp_UpdateCategories
    (@tvpEditedCategories dbo.CategoryTableType READONLY)
AS
BEGIN
	SET NOCOUNT ON
	UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;
END

We realize if we create either a temp table or a traditional Table Variable (inline Table Variable), we cannot use them to access this stored procedure:

Instead, we have to declare a type matcing the user defined Table type:

DECLARE @tvpUpdateCategories AS dbo.CategoryTableType

then we can do the job:

Conclusion:

• we cannot use temp table to access a stored procedure with Table-Valued parameters
• we can use Table Variable to access a stored procedure with Table-Valued parameters, while we have to defined the parameter exactly using the type defined in the store procedure

D.5 - Transaction

Table variables cannot be involved in transactions, logging, or locking. Let us test the Transaction below:

USE [Northwind]
GO

DECLARE @input TABLE
	( 
		CategoryID int NOT NULL PRIMARY KEY,
		CategoryName nvarchar(50) 
	)
INSERT INTO @input([CategoryID], [CategoryName]) VALUES(8,'SeaFood3')

SELECT * FROM @input

DROP TABLE IF EXISTS #input
SELECT * 
INTO #input
FROM @input

SELECT * FROM #input

-- TRANSACTION TEST
BEGIN TRANSACTION
	INSERT INTO #input([CategoryID], [CategoryName]) VALUES(9,'SeaFood4')
	INSERT INTO @input([CategoryID], [CategoryName]) VALUES(9,'SeaFood4')
ROLLBACK TRANSACTION

SELECT * FROM #input
SELECT * FROM @input

Run the script, after transaction is rolling back, the Table Variable is not rollback as shown below:

D.6 - Performance

For performance issue, we ususally talk about large data set, this article, SQL Server Temp Table vs Table Variable Performance Testing (mssqltips.com), gave a testing result:

• a temporary table generally provides better performance than a table variable. 

For small size data set, Table Variable could be much faster like the Table in B suggested, while remember Microsoft says:

• A memory-optimized table variable does not reside in tempdb. Memory-optimization results in speed increases that are often 10 times faster or more.

D.7 - SELECT INTO?

Tables variables can't be used as the target of the INTO clause in a SELECT ... INTO statement. [ref]

Run the test code:

DECLARE @input TABLE
	( 
		CategoryID int NOT NULL PRIMARY KEY,
		CategoryName nvarchar(50) 
	)
INSERT INTO @input([CategoryID], [CategoryName]) VALUES(8,'SeaFood3')

SELECT * FROM @input

DROP TABLE IF EXISTS #input
SELECT * 
INTO #input
FROM @input

SELECT * FROM #input

DECLARE @input1 TABLE
	( 
		CategoryID int NOT NULL PRIMARY KEY,
		CategoryName nvarchar(50) 
	)
INSERT INTO @input1
SELECT * FROM #input

SELECT * FROM @input1

--SELECT * 
--INTO @input2
--FROM @input

The results:

For tem table #input, we do not need to define it first, just use

SELECT * INTO #input FROM @input

However, it does not work for Table Variable, @input, where the red frame indicated. For Table Variable, you have to Declare it first, then you can use

INSERT INTO @input1 SELECT * FROM #input

Like the second Greed frame.

 

References

• Comparisons and Differences:
  • Differences between SQL Server temporary tables and table variables (mssqltips.com)
  • Difference between CTE and Temp Table and Table Variable (scholarhat.com)
  • When to Use Temporary Tables vs. Table Variables (sqlshack.com)
  • Temporary Tables And Table Variables In SQL (c-sharpcorner.com)
  • Common Table Expressions (CTEs) vs Temporary Tables in BigQuery | by Abhik Saha | Medium
• Performance
  • SQL Server Temp Table vs Table Variable Performance Testing (mssqltips.com)
  • SQL Server CTE vs Temp Table vs Table Variable Performance Test (mssqltips.com)
  • Memory optimization for faster temp table and table variables - SQL Server | Microsoft Learn