Temp Table and Table Variable

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table

Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

CREATE TABLE #LocalTemp
(
     UserID int,
     Name varchar(50), 
     Address varchar(150)
)
insert into #LocalTemp values ( 1, 'Shailendra','Noida');

Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

Global Temp Table

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp
(
     UserID int,
     Name varchar(50), 
     Address varchar(150)
)

insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');

Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
     SNo INT IDENTITY(1,1),
     ProductID INT,
     Qty INT
 ) 

 INSERT INTO @TProduct(ProductID,Qty)

 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 

 Select * from @TProduct
 
 Select * from @TProduct --gives error in next batch

Note

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
2. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

Difference between Temp Table and Temp Variable

• Temp table result can be used by multiple users. But the table variable can be used by the current user only.  

•  Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., where table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.

• Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. But the table variable can be used up to that program. (Stored procedure)

• Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. But we cannot do it for table variable.

• Functions cannot use the temp variable. But the function allows us to use the table variable. More over we cannot do the DML operation in the functions but using the table variable we can do that.

• The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Where the table variable won't do like that.

 

Difference between local and global temporary tables

local:

1. denoted by # symbol.
2. valid for the current connection only.
3. They are cleared as soon as the curent connection closes.
4. cannot be shared between multiple users.

global:

1. denoted by ## symbol.
2. Available to all the connections once created.
3. They are cleared when the last connection is closed.
4. can be shared betwen multiple users.

Both of then are stored in the tempdb database