This is the first article in the series of articles on Difference Between Temporary Table and Table Variable. This article lists out difference between Temporary Table and Table Variable.
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.
Difference Between Temporary Table and Table Variable – Summary
Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.
| 1. SYNTAX | |
|
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
|
Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. -- 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: |
| 2. MODIFYING STRUCTURE | |
|
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP. --Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) GO --Add Address Column ALTER TABLE #Customer ADD Address VARCHAR(400) GO --DROP Temporary Table DROP TABLE #Customer GO |
Table Variables 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. |
| 3. 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. | |
| 4. TRANSACTIONS | |
| Temporary Tables honor the explicit transactions defined by the user. | Table variables doesn’t participate in the explicit transactions defined by the user. |
| 5. USER DEFINED FUNCTION | |
| Temporary Tables are not allowed in User Defined Functions. | Table Variables can be used in User Defined Functions. |
| 6. INDEXES | |
| Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint. | Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration. |
| 7. SCOPE | |
| There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure.Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it. | Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes. |
The above listed differences are discussed in-detail with extensive list of examples in the below articles:
- 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.
ALSO READ
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME
I guess there’s a mistake in the point 5 since temporary tables can also be used inside a udf.
Temporary Tables are not allowed in UDF. Please try creating the below function, it will Error out with message: Msg 2772, Level 16, State 1, Procedure TempTable, Line 5
Cannot access temporary tables from within a function.
CREATE FUNCTION [DBO].[TempTable]()
RETURNS INT
AS
BEGIN
CREATE TABLE #t1(col1 INT)
RETURN 1
END
Nice article and examples also good
Now I understood very clearly Difference between TT and TV.
Thanks you Basavaraj
Thank you Sarika
nice example
Great Example
Very Nice Example . Thanks Basavaraj
Thanks Sulekh… Appreciate your comments…