Difference Between Temp Table and Table Variable

Both Temporary Tables ( # Tables) and Table Variables (@ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.

1.Temp Table

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.

Syntax Of Tamp Table

2. Modification OF Temp Table

Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP.
Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped.

3.TRANSACTIONS

Temporary Tables honor the explicit transactions defined by the user.

4. USER DEFINED FUNCTION

Temporary Tables are not allowed in User Defined Functions.

5.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 Variable

Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it.

 MODIFYING STRUCTURE

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.

 TRANSACTIONS

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

USER DEFINED FUNCTION

Table Variables can be used in User Defined Functions.

INDEXES

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.

Difference Between Temp Table and Table Variable

Both Temporary Tables ( # Tables) and Table Variables (@ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.

1.Temp Table

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.

Syntax Of Tamp Table

2. Modification OF Temp Table

Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP.
Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped.

3.TRANSACTIONS

Temporary Tables honor the explicit transactions defined by the user.

4. USER DEFINED FUNCTION

Temporary Tables are not allowed in User Defined Functions.

5.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 Variable

Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it.

 MODIFYING STRUCTURE

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.

 TRANSACTIONS

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

USER DEFINED FUNCTION

Table Variables can be used in User Defined Functions.

INDEXES

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.