Tag Archives: Temporary Tables are Created in TempDB

Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions in Sql Server

Differences, Sql Server, , , , , , ,

This is the second article in the series of articles on Difference Between Temporary Table and Table Variable. This article provides the Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions with extensive list of examples.

Below is the complete list of articles in this series:

Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions

1. SYNTAX

TEMPORARY TABLE

Demo 1: Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table.

-- Create Temporary Table
CREATE TABLE #Customer ( Id INT, Name VARCHAR(50))
--Insert Two records
INSERT INTO #Customer VALUES(1,'Basavaraj') 
INSERT INTO #Customer VALUES(2,'Kalpana')
--Reterive the records
SELECT * FROM #Customer
--DROP Temporary Table
DROP TABLE #Customer
GO

RESULT:
Syntax Temporary Table Example

Demo 2: Temporary Table structure can be changed after it’s creation.Below script creates a Temporary Table #Customer and then after it’s creation adds Address column to it

--Create Temporary Table
CREATE TABLE #Customer ( Id INT, Name VARCHAR(50))
--Add Address Column to the #Customer
ALTER TABLE #Customer ADD Address VARCHAR(400)
GO

TABLE VARIABLE

Demo 1: Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. Table Variable doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.

-- Create Table Variable
DECLARE @Customer TABLE
(
 Id INT,
 Name VARCHAR(50)   
)
--Insert Two records
INSERT INTO @Customer VALUES(1,'Basavaraj') 
INSERT INTO @Customer VALUES(2,'Kalpana')
--Reterive the records
SELECT * FROM @Customer
GO

RESULT:
Syntax Table Variable1

2. STORAGE LOCATION

One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.

Table Variable

Demo 1: Sys.Tables can be used to prove that Table Variables are created in TempDB.

USE MASTER
GO
--Below query lists all the Tables in TempDB
SELECT * From TEMPDB.sys.tables
GO
--Declare a Table Variable
DECLARE @TableVariableLocationDemo TABLE(Name VARCHAR(50))
--Verify whether Table Variable is created in TempDB
SELECT * From TEMPDB.sys.tables
GO

RESULT:
Table Variable Storage Location in TempDB1

From the above result it is clear that the Table Variables are created in TempDb as we are seeing an entry getting into the Sys.Tables of TempDB.

Demo 2: In this demo we will see that the data in the Table Variable is stored in the data pages of the TempDB data file. To prove this I will be using some of the Sql Server Undocumented and Unsupported Sql Server features, so use at your own risk. 

USE MASTER
GO
--Create Table Variable and insert one record in to it
DECLARE @TableVariable TABLE (Name VARCHAR(50)) 
INSERT INTO  @TableVariable VALUES ('Basavaraj Biradar')
--Get the Physical location of the row in the Table Variable
DECLARE @file_id INT, @page_id INT
SELECT @file_id = CAST(CONVERT(binary (2), REVERSE(
			SUBSTRING(%%physloc%%, 5, 2))) AS INT),
	   @page_id = CAST(CONVERT(binary (4), REVERSE(
			SUBSTRING (%%physloc%%, 1, 4))) AS INT)
FROM @TableVariable
--Check whether the Physical location of the Table Variable 
--Row is in the TEMPDB database by looking into the Page dump 
DBCC TRACEON(3604)
--Check FileId and Page ID in TEMPDB
DBCC PAGE('TEMPDB', @file_id, @page_id, 3)
DBCC TRACEOFF(3604)

RESULT:
PhysicalLocationOfTheTableVariableRow1

From the above result it is clear that the physical location of the Table Variable row is in the TempDB database’s data file.

The Undocumented DBCC PAGE statements first parameter passed in the above script is TempDB, the second parameter is the physical file_id of the Table variable row and third parameter is the page_id of the Table Variable row within the data file. Fourth parameter is the level of detailed information required in the output.

Temporary Table

Demo 1: Sys.Tables can be used to prove that Temporary Tables are created in TempDB.

USE MASTER
GO
--Below query lists all the Tables in TempDB
SELECT * From TEMPDB.sys.tables
GO
--Create a Temporary Table
CREATE TABLE #TemporaryTableLocationDemo(Name VARCHAR(50))
--Verify whether Temporary Table is created in TempDB
SELECT * From TEMPDB.sys.tables
GO

RESULT:
Temporary Table Storage Location in TempDB1

Demo 2: In this demo we will see that the data in the Temporary Table is stored in the data page of the TempDB data file. To prove this I will be using some of the Sql Server Undocumented and Unsupported Sql Server features, so use at your own risk.

USE MASTER
GO
--Create Temporary Table and insert one record in it
Create table #TempTable (Name VARCHAR(50)) 
INSERT INTO  #TempTable VALUES ('Basavaraj Biradar')
--Get the Physical location of the row in the Temporary Table
DECLARE @file_id INT, @page_id INT
SELECT @file_id = CAST(CONVERT(binary (2), REVERSE(
			SUBSTRING(%%physloc%%, 5, 2))) AS INT),
	   @page_id = CAST(CONVERT(binary (4), REVERSE(
			SUBSTRING (%%physloc%%, 1, 4))) AS INT)
FROM #TempTable
--Check whether the Physical location of the Temporary Table 
--Row is in the TEMPDB database by looking into the Page dump 
DBCC TRACEON(3604)
--Check FileId and Page ID in TEMPDB
DBCC PAGE('TEMPDB', @file_id, @page_id, 3)
DBCC TRACEOFF(3604)

RESULT:
PhysicalLocationOfTheTemporaryTablleRow1

From the above result it is clear that the physical location of the Temporary Table row is also in the TempDB database’s data file.

3. TRANSACTION

Table variables doesn’t participate in the explicit transactions defined by the user, where as Temporary Tables honor the Transactions.

Table Variable

Demo 1: Table variables doesn’t participate in the explicit transactions defined by the user.

--Declare Table Variable 
DECLARE @TableVariableTransactionDemo TABLE (Name VARCHAR(50)) 
--Start the Transaction
BEGIN TRAN
   --Insert a Record in the Table Variable
   INSERT INTO  @TableVariableTransactionDemo
   VALUES ('Basavaraj Biradar')
   --Rollback the Transaction
ROLLBACK TRAN
--Verify the records in the Table Variable
SELECT * FROM @TableVariableTransactionDemo
GO

RESULT: ROLLBACK TRAN statement didn’t revert back the record inserted in the Table variable within a Transaction.

Transaction Table Variable

Temporary Table

Demo 1: Table variables doesn’t participate in the explicit transactions defined by the user.

--Create Temporary Table 
CREATE TABLE #TemporaryTableTransactionDemo (Name VARCHAR(50)) 
--Start the Transaction
BEGIN TRAN
   --Insert a Record in the Temporary Table
   INSERT INTO  #TemporaryTableTransactionDemo
   VALUES ('Basavaraj Biradar')
  --Rollback the Transaction
ROLLBACK TRAN
--Verify the records in the Temporary Table
SELECT * FROM #TemporaryTableTransactionDemo
GO

RESULT: ROLLBACK TRAN statement reverted back the record which is inserted in the Temporary Table within a Transaction.

Transaction Temporary Table

You may like to read the other articles in this Series of articles on Difference Between Temporary Table and Table Variable: