There are three types of temporary tables in SQL Server. The local temporary table, the global temporary table and the table variable.
Everyone of them has a use case and advantages and disadvantages.
First things first. Despite popular belief ALL three types are stored in tempdb. We will prove that so you don't have to believe just my words.
Local temporary table
A local temporary table can be created by using a hash before stating the table name omitting the schema.
CREATE TABLE #tempTable1 (
id INT,
val VARCHAR(10)
);
Scope
A local temporary table only 'lives' until the end of the user session (i.e. a tab in SSMS) or when used in a stored procedure it lasts until the end of that stored procedure.
Location
It is stored in tempdb. See the code below:
SELECT *
FROM tempdb.INFORMATION_SCHEMA.TABLES
Notes
SQL Server builds statistics on the table and therefore the optimizer handles a local temporary variable as normal table. You can also create clustered and non clustered indexes on that kind of table.
To prove that SQL Server builds statistics on temporary tables we fill the table with two rows:
INSERT INTO #tempTable1
VALUES
(1,'1'),
(2,'2');
Now we do a self join so the optimizer is forced to build statistics in order to find a good query plan:
SELECT *
FROM #tempTable1 AS a
JOIN #tempTable1 AS b
ON a.id = b.id;
Now lets query the statistics on the column 'id'
DBCC SHOW_STATISTICS ('tempdb..#tempTable1','id')Global temporary table
Basically it is the same like local temporary tables. The only difference is the scope. Global temporary tables are introduced with double hashes like ##temptable
Scope
All Connections can see the table so be careful to choose a name. A good approach is to somehow involve a GUID to make the name unique.
Table Variables
Table variables differ greatly from temporary table. In fact the only real similarity is that it is also stored in tempdb as we can see here.
Location
SELECT *
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO
DECLARE @tableVar TABLE (
id INT,
val VARCHAR(10)
);
SELECT *
FROM tempdb.INFORMATION_SCHEMA.TABLES
Scope
The scope of a table variable is just the batch or the stored procedure that it used in.
Another important note is that a table variable is not affected by transactions that means it is also NOT affected by rollbacks.
See an example below:
DECLARE @tableVar TABLE (
id INT,
val VARCHAR(10)
);
INSERT INTO @tableVar VALUES (1,'1');
SELECT * FROM @tableVar;
BEGIN TRAN
UPDATE @tableVar SET val = '2';
ROLLBACK
SELECT * FROM @tableVar;
Notes
Last remark on table variables: SQL Server does not maintain statistics on them so the table variable is just a black box for the query optimizer hence there will be only table scans of a table variable.
Summary
| Storage | Scope | statistics | use case |
|---|
| temporary table | tempdb | user session (inner), SP | yes | one time tables with many rows (>500) |
| gloal temporary table | tempdb | user session (until no references) | yes | avoid until needed!, suitable in SSIS packages |
| table variables | tempdb | batch, SP | no | one time tables with few rows (<500) |
