Temporary tables and table variables are two mechanisms to store and manipulate data within SQL Server, in general they work similarly but there are some differences between them.
Temporary tables are physical tables that are created in the tempdb database and their data is stored in disk pages, similar to regular tables. They can be created using the ‘CREATE TABLE‘ statement with the ‘#‘ or ‘##‘ prefix to indicate that they are temporary. Temporary tables can be used to store data temporarily during a session, for instance to break down complicated queries into smaller steps or to store intermediate results. A temporary table can be accessed by multiple users simultaneously, as long as they are within the same session. Once the session ends, the temporary table is dropped and its data is removed from the tempdb database. Temporary tables can have indexes, constraints, and triggers like regular tables.
Table variables are basically variables that hold a table-level object, a table variable is used like a regular variable in Transact-SQL (T-SQL) code, they don’t require an explicit drop statement and their scope is limited to the current batch, procedure, or trigger that created them. Table variables store their data in memory, which makes them efficient for small to medium-sized datasets or when dealing with small working sets, so table variables can be used when a temporary table would be overkill. Table variables do not support indexes or constraints, so their data is accessed using table scans and they may have poor performance if their size exceeds a certain limit (memory pressure).
A basic comparison between them would be:
- Temporary Tables:
- Data stored in ‘tempdb‘ on disk.
- Can be accessed by multiple sessions.
- Can have indexes, constraints, and triggers.
- Require explicit creation and removal.
- Optimized for large data sets.
- Table Variables:
- Data stored in memory.
- Limited to the current batch or scope.
- Do not support indexes or constraints.
- Automatically cleaned up after use.
- Optimized for small to medium-sized data sets.
Here is an example code showing how to use both of these concepts:
--Using Temporary Tables
USE AdventureWorks2019;
GO
CREATE TABLE #Temp
(
ID int PRIMARY KEY,
Name varchar(50)
);
INSERT INTO #Temp(ID, Name)
SELECT ProductID, Name
FROM Production.Product;
SELECT *
FROM #Temp
WHERE ID BETWEEN 50 AND 55;
DROP TABLE #Temp;
GO
--Using Table Variables
DECLARE @TableVar AS TABLE
(
ID int PRIMARY KEY,
Name varchar(50)
);
INSERT INTO @TableVar(ID, Name)
SELECT TOP 10 ProductID, Name
FROM Production.Product
ORDER BY ProductID;
SELECT *
FROM @TableVar
WHERE ID BETWEEN 50 AND 55; In this code, we create a temporary table ‘#Temp‘ and store data from the ‘Production.Product‘ table in it. We then select a subset of the data from ‘#Temp‘ based on a criterion before dropping the table. In the second part of the code, we declare a table variable ‘@TableVar‘ and store 10 rows from ‘Production.Product‘ in it using a ‘SELECT‘ statement with an ‘ORDER BY‘ clause. We then select a subset of the data from ‘@TableVar‘ based on a criterion.
Overall, both temporary tables and table variables are useful tools in SQL Server and each has its own strengths and weaknesses. The choice between them depends on the specific requirements of the situation at hand.