Temporary Tables In SQL Server

Often as a developer, we come upon a situation where we want to store some intermediate results temporarily. So in such cases, Sql Server provides us with a feature called temporary tables which can be created in run time and used to store the data temporarily.

Why do we need temporary tables in SQL Server?

Often as a developer, we come upon a situation where we want to store some intermediate results temporarily. So in such cases, SQL Server provides us with a feature called temporary tables which can be created at runtime and used to store the data temporarily.

• They can be used to create a workspace for processing the data inside stored procedure or functions.
• They can be used for complex joins.
• They get created either in ‘tempdb’ system database available SQL Server or in memory based on the choice made by query engine based on the size of the data getting stored.
• All the operations which can be performed on a normal table can be performed on a temporary table as well.

Types of Temporary Tables

Based on the scope of the temporary table, we have two types of temporary tables in SQL Server.

• Local temporary tables
• Global temporary tables

Local Temporary table

• We can create a local temporary table by using # before the table name.
• They are available only for the current user session.
• They get discarded automatically once the session has ended.
  

  /*Script for creating local temporary table*/
  CREATE TABLE# temptable(ID int, Name varchar(50), )
  GO
  insert into# temptable values(1, 'Padmalatha');
  GO
  Select * from# temptable

Once this query is executed, we can see that the temporary table named ‘temptable’ is created in ‘tempdb’ database inside ‘Temporary Tables’ folder and results are displayed as shown below.

Local temporary table created

Results

Global Temporary Table

• We can create the global temporary tables using ## before the table name.
• We can store the data in a global temporary table when we want to access the same data from different sessions. They are available for all users.
• It gets discarded automatically when there is no active reference for that table.
  

  /*Script for creating Global temporary table*/
  CREATE TABLE## globaltemp(TotalCount int, )
  GO
  insert into## globaltemp values(100);
  GO
  Select * from## globaltemp

Once this query is executed, we can see that the global temporary table named ‘globaltemp’ is created in ‘tempdb’ database inside ‘Temporary Tables’ folder and results are displayed as shown below.

global temporary table created

Results

Table Variable

• They are alternative options to temporary tables.
• Their scope is limited only to batch or stored procedure or function in which they are declared.
• ­They are declared using @ before a variable and table datatype.
• Transaction logs are not created for table variables.

When can we go for Table Variable?

We can use table variable if the quantity of the data being stored is less. Indexes cannot be created on the table variable. So if we have a large quantity of data to be stored, then it is better to use temporary tables.

/*Script for creating table variable*/
Declare @SampleTableVar table(Id int primary key, Total_Count int)
Insert @SampleTableVar values(1, 20)
Insert @SampleTableVar values(2, 30)
Insert @SampleTableVar values(3, 40)
Select * from @SampleTableVar
Go

Results