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:
- Difference Between Temporary Table and Table Variable – Summary
- Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions.
- Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope.
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
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
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
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)
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
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)
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.
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.
You may like to read the other articles in this Series of articles on Difference Between Temporary Table and Table Variable:
Really enjoyed reading ur blog.
Thank you so much Basavaraj !
Now, I have no doubt about table variable storage.
Temporary Table
Demo 1: Table variables doesn’t participate in the explicit transactions defined by the user.
Comments are updated properly for above line.
Nice explanation. I liked reading the blog..
well explanation…..please add as well CTE (no need to go other site(s) for cte )
Karthick, You can find information on CTE @
https://sqlhints.com/2015/09/05/introduction-to-common-table-expression-a-k-a-cte-in-sql-server/