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:
- Local Temporary Table
- Global Temporary Table
- 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
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+')
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