Temporary Table and Table Variable

Introduction:
This document describes about the comparison and advantages between Temporary Table and Table Variable with extensive list of examples.

SQL Operations:
Below is the example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table

Execution and output:

Support of DDL statements:
We can use DDL statements ALTER, CREATE, DROP on Temporary Table, but Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP.

Storage:
Temporary Tables:
Temporary Tables are created in TempDB

Variable Tables:
  Table Variables are created In-Memory
  ![temp.png](https://ucarecdn.com/eb5a284d-26b2-4bbc-af29-d66facdd2a5c/)

Scope:

Temporary Tables:
There are two types of Temporary Tables,

1.	Local Temporary Tables 
a.	Whose name starts with single # sign
b.	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.
c.	If a Local Temporary Table is created within a stored procedure then it can be accessed in its child stored procedures, but it can’t be accessed outside the stored procedure.

2.	Global Temporary Tables 
a.	Whose name starts with two # signs. 
b.	Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. 
c.	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.

Variable Tables:
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.

User Defined Function:
Temporary tables:
Temporary Tables are not allowed in User Defined Functions.
Variable Tables:
Table Variables can be used in User Defined Functions.

Indexes:
Temporary tables:
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.

Variable Tables:
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.

Transactions:
Temporary tables:
Table variables doesn’t participate in the explicit transactions defined by the user, where as Temporary Tables honor the Transactions.
In below example, ROLLBACK TRAN statement didn’t revert back the record inserted in the Table variable within a Transaction.

Variable Tables:
Table variables doesn’t participate in the explicit transactions defined by the user.
In below example, ROLLBACK TRAN statement reverted back the record which is inserted in the Temporary Table within a Transaction.