Temporary Tables And Table Variables In SQL

Temporary tables and table variables, both have their own pros and cons. We need to decide which one to use and when.

Let us compile the list for differences.

⇒ Table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.

⇒ Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table.

⇒ Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

⇒ Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.

⇒ Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.

⇒ Temporary table allows Schema modifications unlike Table variables.

Table Variable in SQL Server – Example

Table variable is a very useful programming construct, like that of any other variable.

1.  DECLARE @TStudent TABLE  
2.  (  
3.     RollNo INT IDENTITY(1,1),  
4.     StudentID INT,  
5.     Name INT  
6.  )   
7.  --Insert data to Table variable @TStudent   
8.  INSERT INTO @TStudent(StudentID,Name)  
9.  SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
10.    
11.  --Select data from Table variable @TStudent  
12.  SELECT * FROM @TStudent  
13.    
14.  --Next batch  
15.  GO  
16.  SELECT * FROM @TStudent --gives error  
17. DECLARE @TStudent TABLE  
18.  (  
19.     RollNo INT IDENTITY(1,1),  
20.     StudentID INT,  
21.     Name INT  
22.  )   
23.  --Insert data to Table variable @TStudent   
24.  INSERT INTO @TStudent(StudentID,Name)  
25.  SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
26.    
27.  --Select data from Table variable @TStudent  
28.  SELECT * FROM @TStudent  
29.    
30.  --Next batch  
31.  GO  
32.  SELECT * FROM @TStudent --gives error   

Temporary Tables in SQL Server – Example

In SQL Server, based on the scope and behavior, temporary tables are of two types, 

1. Local Temporary Tables (#temp)  
2. Global Temporary Tables (##temp)  
3.   
4. CREATE TABLE #StudentTemp  
5. (  
6.     StudentID int,  
7.     Name varchar(50),   
8.     Address varchar(150)  
9. )  
10. GO  
11. INSERT INTO #StudentTemp VALUES ( 1, 'Dipendra','Pune');  
12. GO  
13. SELECT * FROM #StudentTemp  
14. CREATE TABLE #StudentTemp  
15. (  
16.     StudentID int,  
17.     Name varchar(50),   
18.     Address varchar(150)  
19. )  
20. GO  
21. INSERT INTO #StudentTemp VALUES ( 1, 'Dipendra','Pune');  
22. GO  
23. SELECT * FROM #StudentTemp   

Points to Remember

• Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
  
• Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.
  
• Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.

I hope I was able to explain the difference between Temporary Tables and Table variables in SQL Server.

Happy Reading!