Temp Table vs Table Variable

There are scenarios in which we need temporary storage to do some operations. That is to store data for some operations and then we no longer need that data.

We have the following three temporary storage locations:

1. Local Temporary Table
2. Global Temporary Table
3. Table Variable

We will see their features and how and when to use which one respectively.

Temporary Table

Temporary tables are similar to permanent tables, except temporary tables are stored in a TempDB and are deleted automatically when no longer in use.

#Local Temp Table (#table_name )

• The Local temp table is declared with a (#) sign.
  
  
• A Local temp table exists for the duration of the current user session or until all the procedures that use the #temp_table frees them.
  
  
• As the user who created it goes offline or the procedure that used the #temp_table completes, the Local temp table no longer exists.
  
  
• A single user can access a temp_table because it is local to one user session.

Create Local Temp Table

CREATE TABLE #Employee
(
Employee_ID INT,
Employee_Name VARCHAR(50),
Employee_Salary BIGINT,
Employee_Designation VARCHAR(50),
Employee_BloodGroup VARCHAR(10)
)

Inserting records into a Temp Table:

insert into #Employee values (1,'John',10,'Developer','A+')
insert into #Employee values (2,'Isbella',10,'Team Lead','B+')
insert into #Employee values (3,'Danial',10,'Mobile','AB+')
insert into #Employee values (4,'Umer',10,'Manager','O+')
insert into #Employee values (5,'Kashif',10,'Master','O-')

Select from Temp Table

 

Select * from #Employee

Alter Temp Table



Here I need to add another column to my temp table. So I have altered my table structure and selected records and our newly added column is now appearing here.

The Alter command is used to alter tables.

ALTER TABLE #Employee ADD Adress VARCHAR(50)
Select * from #Employee

Dropping Temp Table Explicitly


DROP TABLE #Employee

Now, as described earlier a Local temp table is available only with the current user session, to prove this point have a look:

 

Here what I did is opened in a new query window and tried to access the output local temp table and here we get no Object name. Because it is out of the scope of the current session.

##Global Temp Table (##table_name )

• The Global temp table is declared with a (##) sign.
  
  
• A Global temp table exists for the duration of the current user session or until all the procedures that use a #global_temp_table frees them.
  
  
• As the last user who is engaged with a ##global_temp_table session expires, the ##global_temp_table does not exist any more.

Create Global Temporary Table

CREATE TABLE ##EmployeeTable
(
Employee_ID INT,
Employee_Name VARCHAR(50),
Employee_Salary BIGINT,
Employee_Designation VARCHAR(50),
Employee_BloodGroup VARCHAR(10)
)

insert into ##EmployeeTable values (1,'John',10,'Developer','A+')
insert into ##EmployeeTable values (2,'Isbella',10,'Team Lead','B+')
insert into ##EmployeeTable values (3,'Danial',10,'Mobile','AB+')
insert into ##EmployeeTable values (4,'Umer',10,'Manager','O+')

select * from ##EmployeeTable

DROP TABLE ##EmployeeTable

The Global temp table is the same as a local temp in creation, insertion and manipulation type things except with the difference of a little declaration syntax. What I did here is:

• Created a Global temp table (the same as a Local temp table except with two (##) signs)
• Inserted records in it
• Selected records from it

Then opened a new query editor window and here I get the table not as a local temp table where we are unable to do so. A Global temp table exists across the current session.

Table Variable

Create Table Variable

DECLARE @Employee TABLE
(
Employee_ID INT,
Employee_Name VARCHAR(50),
Employee_Salary BIGINT,
Employee_Designation VARCHAR(50),
Employee_BloodGroup VARCHAR(10)
)

Insert into Table Variable

DECLARE @Employee TABLE
(
Employee_ID INT,
Employee_Name VARCHAR(50),
Employee_Salary BIGINT,
Employee_Designation VARCHAR(50),
Employee_BloodGroup VARCHAR(10)
)

insert into @Employee values (1,'John',10,'Developer','A+')
insert into @Employee values (2,'Isbella',10,'Team Lead','B+')
insert into @Employee values (3,'Danial',10,'Mobile','AB+')
insert into @Employee values (4,'Umer',10,'Manager','O+')

Alter Table Variable

DECLARE @Employee TABLE
(
Employee_ID INT,
Employee_Name VARCHAR(50),
Employee_Salary BIGINT,
Employee_Designation VARCHAR(50),
Employee_BloodGroup VARCHAR(10)
)

insert into @Employee values (1,'John',10,'Developer','A+')
insert into @Employee values (2,'Isbella',10,'Team Lead','B+')
insert into @Employee values (3,'Danial',10,'Mobile','AB+')
insert into @Employee values (4,'Umer',10,'Manager','O+')

alter table @Employee Add Adress varchar (30)

The DDL commands Create, Later and Drop cannot be used with a Table variable.

Select from Table Variable

DECLARE @Employee TABLE
(
Employee_ID INT,
Employee_Name VARCHAR(50),
Employee_Salary BIGINT,
Employee_Designation VARCHAR(50),
Employee_BloodGroup VARCHAR(10)
)

insert into @Employee values (1,'John',10,'Developer','A+')
insert into @Employee values (2,'Isbella',10,'Team Lead','B+')
insert into @Employee values (3,'Danial',10,'Mobile','AB+')
insert into @Employee values (4,'Umer',10,'Manager','O+')

SELECT * FROM @Employee

Temp table Vs. Table Variable Comparison

Transaction

Temp tables support transactions.


As in the preceding image, I created a temp table then I began my transaction and inserted some records in it and after insertion I rolled back my transaction and later I tried selecting the records and here we see our transaction is being rolled back, so temp tables do support transactions.


As in the case of a table variable we performed the same procedure and yet we see transactions did not work for it.

Location in Databases

Both temp tables and table variables are stored in TempDB.

DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)

INSERT INTO @TableVariable DEFAULT VALUES

WAITFOR DELAY '00:00:10'
CREATE TABLE #TempTable (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO #TempTable DEFAULT VALUES

SELECT DT AS TableVariableTime FROM @TableVariable
SELECT DT AS TempTableTime FROM #TempTable

SELECT * FROM tempdb.sys.objects
WHERE type = 'U'

DROP TABLE #TempTable