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

 

Submit a Comment

Your email address will not be published.

Subscribe

Select Categories