Difference Between Temp Table and Table Variable in SQL

Temporary tables in SQL:

A temporary table in SQL, as the name suggests, is a database table that persists temporarily in the database.
Temporary table can perform all the actions as that of a normal table
If we create a temporary table during the execution of a stored procedure, it is destroyed when the execution of the procedure is completed
One example as to when you can use a temporary table is when you have a large number of joins. You can use minimum joins and store data in temporary table and then again use the temp table to join to another table. We can also create indexes for better performance on temporary table
Temporary table are stored in “tempdb” of the database under the “System Databases” section
There are two types of temporary table based on the scope and behavior :
1. Local temporary table
2. Global temporary table

Local temp table:

Local temp tables are only available to the current connection for the user
It is deleted when the user disconnects from the instance (termination from any process)
To create a local temp table we precede the table name with a single hash (“#”) sign
Syntax:

create table #table_name
(            
    column_name  data_type,
    column_name  data_type
)

Example:

Create table #Student
(
    StudentId int,
    StudentName varchar(50)
)

Global temp table:

A global temp table is available to all sessions and users
It is deleted only when all connections are closed
To create a global temp table we precede the table name with a double hash (“##”) sign
Syntax:

create table ##table_name
(
    column_name  data_type,
    column_name  data_type
)

Example:

Create table ##Student
(
    StudentId int,
    StudentName varchar(50)
)

Table variable in SQL:

The table variable is a local variable that stored data temporarily
It is similar to the temp table but as certain limitations like we cannot create indexes on table variable
It exists for a particular batch of query executions and gets destroyed once the batch is executed
It is created using declare keyword just like any variable and the table name is preceded using @ sign
Syntax:

Declare @table_name table
(
    Column_name data_type,
    Column_name data_type
)

Example:

Declare @Student table
(
    StudentId int,
    StudentName varchar(50)
)

It is stored in the “tempdb” and not in the memory. Since the lifecycle of the table variable is only for a batch, the table variable is dropped and hence there is a misconception that it is stored in a batch
Following code can help us understand the same concept

DECLARE @StudentTb TABLE
(
    StudentId INT, StudentName VARCHAR(40)
);

SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS

insert into @Studenttb values(1,'Test')

select * from @StudentTb   

GO

--this line will give an error as a new batch execution is started with Go
-- select * from @StudentTb


--table is not avaliable with a new batch

SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS

Output:

Difference between Temp table and Table variable in SQL:

Point of comparison	Temp table	Temporary variable
Scope	A temp table remain until the instance is alive or all sessions are closed depending upon the type of the temp table (local or global temp table)	A temporary variable remains only for any particular batch execution in which it is created
Indexes	We can create index on temp table as any normal SQL table	It is not possible to create explicit indexes on temporary variable. However, we can have implicit indexes in form of constraints such as primary constraint or unique constraint
Transactions	Temp table supports transactions	Table variable does not support transactions
DDL Commands	A temp table supports DDL commands. For example, we can alter a temp table after it is created	A temporary variable does not support DDL commands. For example, we cannot alter a table created using a temp variable
Truncate	A temp table can be truncated	A temporary variable cannot be truncated
Functions	A temp table cannot be used with user defined functions	A temporary variable can be used with user defined functions