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: