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

EmpTable

  1. CREATE TABLE #Employee  
  2. (   
  3. Employee_ID INT,   
  4. Employee_Name VARCHAR(50),  
  5. Employee_Salary BIGINT,   
  6. Employee_Designation VARCHAR(50),  
  7. Employee_BloodGroup VARCHAR(10)  
  8. )  
Inserting records into a Temp Table:

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

 

Select

  1. Select * from #Employee  
Alter Temp Table

AlterTable

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.
  1. ALTER TABLE #Employee ADD Adress VARCHAR(50)  
  2. Select * from #Employee  
Dropping Temp Table Explicitly

DropTable
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:

 

LocalTemp

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.
TempTable

Create Global Temporary Table
  1. CREATE TABLE ##EmployeeTable  
  2. (   
  3. Employee_ID INT,   
  4. Employee_Name VARCHAR(50),  
  5. Employee_Salary BIGINT,   
  6. Employee_Designation VARCHAR(50),  
  7. Employee_BloodGroup VARCHAR(10)  
  8. )  
  9.   
  10. insert into ##EmployeeTable values (1,'John',10,'Developer','A+')  
  11. insert into ##EmployeeTable values (2,'Isbella',10,'Team Lead','B+')  
  12. insert into ##EmployeeTable values (3,'Danial',10,'Mobile','AB+')  
  13. insert into ##EmployeeTable values (4,'Umer',10,'Manager','O+')  
  14.   
  15. select * from ##EmployeeTable  
  16.   
  17. 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

CreateVariable

  1. DECLARE @Employee TABLE  
  2. (  
  3. Employee_ID INT,   
  4. Employee_Name VARCHAR(50),  
  5. Employee_Salary BIGINT,   
  6. Employee_Designation VARCHAR(50),  
  7. Employee_BloodGroup VARCHAR(10)   
  8. )  
Insert into Table Variable

InsertVariable
  1. DECLARE @Employee TABLE  
  2. (  
  3. Employee_ID INT,   
  4. Employee_Name VARCHAR(50),  
  5. Employee_Salary BIGINT,   
  6. Employee_Designation VARCHAR(50),  
  7. Employee_BloodGroup VARCHAR(10)   
  8. )  
  9.   
  10. insert into @Employee values (1,'John',10,'Developer','A+')  
  11. insert into @Employee values (2,'Isbella',10,'Team Lead','B+')  
  12. insert into @Employee values (3,'Danial',10,'Mobile','AB+')  
  13. insert into @Employee values (4,'Umer',10,'Manager','O+')  
Alter Table Variable

AlterVariable
  1. DECLARE @Employee TABLE  
  2. (  
  3. Employee_ID INT,   
  4. Employee_Name VARCHAR(50),  
  5. Employee_Salary BIGINT,   
  6. Employee_Designation VARCHAR(50),  
  7. Employee_BloodGroup VARCHAR(10)   
  8. )  
  9.   
  10. insert into @Employee values (1,'John',10,'Developer','A+')  
  11. insert into @Employee values (2,'Isbella',10,'Team Lead','B+')  
  12. insert into @Employee values (3,'Danial',10,'Mobile','AB+')  
  13. insert into @Employee values (4,'Umer',10,'Manager','O+')  
  14.   
  15. 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

SelectTableVari
  1. DECLARE @Employee TABLE  
  2. (  
  3. Employee_ID INT,   
  4. Employee_Name VARCHAR(50),  
  5. Employee_Salary BIGINT,   
  6. Employee_Designation VARCHAR(50),  
  7. Employee_BloodGroup VARCHAR(10)   
  8. )  
  9.   
  10. insert into @Employee values (1,'John',10,'Developer','A+')  
  11. insert into @Employee values (2,'Isbella',10,'Team Lead','B+')  
  12. insert into @Employee values (3,'Danial',10,'Mobile','AB+')  
  13. insert into @Employee values (4,'Umer',10,'Manager','O+')  
  1. SELECT * FROM @Employee  
Temp table Vs. Table Variable Comparison

Transaction

Temp tables support transactions.

TableTransaction
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.

DeclareTable
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.
  1. DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)   
  2.   
  3. INSERT INTO @TableVariable DEFAULT VALUES   
  4.   
  5. WAITFOR DELAY '00:00:10'   
  6. CREATE TABLE #TempTable (DT DateTime DEFAULT GETDATE() NOT NULL)   
  7. INSERT INTO #TempTable DEFAULT VALUES   
  8.   
  9. SELECT DT AS TableVariableTime FROM @TableVariable   
  10. SELECT DT AS TempTableTime FROM #TempTable   
  11.   
  12. SELECT * FROM tempdb.sys.objects   
  13. WHERE type = 'U'   
  14.   
  15. DROP TABLE #TempTable  

 

Up Next
    Ebook Download
    View all
    Learn
    View all