Differences Between SQL Server Temporary Tables and Table Variables

What are Temporary Tables in SQL Server?

Temporary Tables are tables that are temporarily created for a particular session. Once the session is terminated, the temporary tables are automatically deleted. In other words, these are the physical tables, which are created in tempdb database in SQL Server.

What are Table Variables in SQL Server?

Table variables are laid out like tables. They are partially stored both in the memory and in the disk.

Array Of Differences Between Temp tables and Table Variables in SQL Server

In this section, we have listed the major differences between Temporary Tables and Table Variables. They are

1. Syntax

The syntax for creating Temporary Table and Table Variable differs largely.

How to Create Temporary Table in SQL Server?

-- Create Temporary Table

CREATE TABLE #Student

(Id INT, Name VARCHAR(50))

--Insert Two records

INSERT INTO #Student

VALUES(1,'Name1')

INSERT INTO #Student

VALUES(2,'Name2')

--Retrieve the records

SELECT * FROM #Student

--DROP Temporary Table

DROP TABLE #Student

GO

How to Create Table Variable in SQL Server?

-- Create Table Variable

DECLARE @Student TABLE

(

 Id INT,

 Name VARCHAR(50) 

)

--Insert Two records

INSERT INTO @Student

VALUES(1,'Name1')

INSERT INTO @Student

VALUES(2,'Name2')

--Retrieve the records

SELECT* FROM @Student

GO

2. Types of Temporary Table in SQL Server

There are mainly two types of Temporary Tables-Local & Global Temporary Tables.

Local Temporary Table: These tables are only available for the session that has created them. Once the session is terminated, these tables are automatically deleted. They can be also be deleted explicitly.
Global Temporary Table: These tables are available for all the sessions and users. They are not deleted until the last session using them is terminated. Similar to local Temporary Table, a user can delete them explicitly.
Table Variable

They can be declared in batch or stored procedure. Unlike Temporary Tables, they cannot be dropped explicitly. Once the batch execution is finished, the Table Variables are dropped automatically.

3. Storage Location of a Temporary Table

The Temporary Tables are stored in tempdb database of SQL server.

Table Variable

The Table Variables are stored in both the memory and the disk in the tempdb database.

4. Structure Modification

Temporary Table

The structure of Temporary Tables can be created even after its creation. Thus, we can use DDL statements like ALTER, DROP and CREATE as shown in the below-mentioned example. In the example we have created a Temporary Table named as Student. In this we will add an Address column and then finally drop the table.

--Create Temporary Table

  CREATE TABLE #Student

  (Id INT, Name VARCHAR(50))

  GO

  --Add Address Column

  ALTER TABLE #Student

  ADD Address VARCHAR(400)

  GO

  --DROP Temporary Table

  DROP TABLE #Student

  GO

Table Variable

The structure of Table Variables cannot be changed once they are created. Thus, it means that DDL commands cannot be run in Table Variables.

5. User Defined Functions

Temporary Table

They are not allowed in the user-defined functions.

Table Variable

The table variables can be used in user-defined functions.

6. Transactions

Temporary Table

They support the explicit transactions that are defined by the user.

Table Variable

They do not participate in the transactions that have been explicitly defined by the user.

7. Indexes

Temporary Table

Local and Global Temporary Tables support creation of indexes on them in order to increase the performance.

Table Variable

Table Variables do not allow creation of indexes on them.

8. Locking

Temporary Tables

Since the Temporary Tables are physical tables, while reading from the table, SQL Optimizer puts a read lock on the table.

Table Variable

Since the Table Variables are partially stored in the memory, they cannot be accessed by any other user or process that the current user. Therefore, no read lock is put on the Table Variable.