Note: this article is published on 08/01/2024.
This series of articles is about SQL Tables.
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 --- #
- 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.
- 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.
- 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.
- 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).
- 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).
- 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).
- A temporary table allows Schema modifications, unlike Table variables (see D.3: Index, bottom Note) --- this is the same as 3.
- 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:
- Performance