Difference between Temporary table and table variable

Difference between 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-

1.      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)

)

GO

insert into #LocalTemp values ( 1, 'Raghavendra','Dasara');

GO

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.

2.      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)

)

GO

insert into ##GlobalTemp values ( 1, 'Raghavendra','Dasara');

GO

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 data to Table variable @Product 

 INSERT INTO @TProduct(ProductID,Qty)

 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 

 --Select data

 Select * from @TProduct

 --Next batch

 GO

 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.