Post
Ask Question

Temporary Tables in SQL Server

Introduction

Temporary tables are tables which are created at run time. Can perform all kind of operations on temp tables like normal table but scope is limited. These are stored in temporary tables.

Temporary tables are two types:

  1. Local Temp Tables
  2. Global Temp Tables

Local Temp Tables:

These are accessed with current connection only. These will delete when user disconnected with instance and prefixed with '#' sign.

Global Variables:

These are accessed with any connection. These will delete when all connections are closed and prefixed with '##' sign.

Example for Temp Tables:

  1. CREATE TABLE #MYTABLE   
  2. (DID INT,  
  3.    DNAME VARCHAR(10),  
  4.    DLOC VARCHAR(10)  
  5. )  
Operations on Temp Table:
  1. INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME) VALUES (14,'CSE','1ST FLOOR')  
  2.   
  3. SELECT * FROM #MYTABLE  
  4.   
  5. UPDATE #MTTABLE SET DNAME = 'ECE', DLOC = '4TH FLOOR' WHERE DID = 14  
  6.   
  7. DELETE FROM #MYTABLE WHERE DID = 14  
Example for Temp Variables:

These are also like temp tables, can be created and can perform all kind of operations like normal tables.
  1. BEGIN  
  2.   
  3. DECLARE @TEMPTABLEVARIABLE TABLE (DID INT,DNAME VARCHAR(10),DLOC VARCHAR(100))        
  4.   
  5. INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME,DLOC) VALUES (14,'CSE','3RD FLOOR')  
  6.   
  7. INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME,DLOC) VALUES (15,'IT','1ST FLOOR')  
  8.   
  9. SELECT * FROM @TEMPTABLEVARIABLE  
  10.   
  11. UPDATE @TEMPTABLEVARIABLE SET DLOC = '5TH FLOOR' WHERE DID = 14  
  12.   
  13. SELECT * FROM @TEMPTABLEVARIABLE  
  14.   
  15. DELETE FROM @TEMPTABLEVARIABLE   WHERE DID = 14  
  16.   
  17.   
  18. SELECT * FROM @TEMPTABLEVARIABLE  
  19.   
  20. END  
Difference between Temp Table and Temp Variable:

Let us observe below example once:
  1. BEGIN  
  2. DECLARE @VAR1 INT  
  3.   
  4. SET @VAR1 = 1  
  5.   
  6. SELECT @VAR1 BEFORETRANSACTION  
  7.   
  8. BEGIN TRAN  
  9.   
  10. SET @VAR1 = 2  
  11.   
  12. ROLLBACK  
  13.   
  14. SELECT @VAR1 AFTERTRANSACTION  
  15. END  
If you observe care fully local variable does not affected / unafftected by roll back statement. If temp table variable is really a variable, can have same behavior. See below once.
  1. BEGIN  
  2.   
  3. IF OBJECT_ID('TEMPDB.DBO.#MYTABLE') IS NOT NULL DROP TABLE #MYTABLE  
  4. CREATE TABLE #MYTABLE (DID INT,DNAME VARCHAR(10))  
  5. INSERT INTO #MYTABLE(DID,DNAME) VALUES (12,'IT')  
  6.   
  7.   
  8. DECLARE @TEMPTABLEVARIABLE TABLE (DID INT,DNAME VARCHAR(10))          
  9. INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME) VALUES (14,'CSE')  
  10.   
  11.   
  12. SELECT DID,DNAME FROM #MYTABLE  
  13.   
  14. SELECT DID,DNAME FROM @TEMPTABLEVARIABLE  
  15.   
  16. BEGIN TRAN  
  17.   
  18. INSERT INTO #MYTABLE(DID,DNAME) VALUES (15,'ECE')  
  19.   
  20. INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME) VALUES (16,'EEE')  
  21.   
  22. ROLLBACK  
  23.   
  24. SELECT * FROM #MYTABLE  
  25.   
  26. SELECT * FROM @TEMPTABLEVARIABLE  
  27.   
  28. END  
Conclusion

Here need to understand Temp Table variable are not affected by rollback whereas Temp Table is affected by rollback. So Temp Table Variable is variable which can stores multiple records, with number of columns.