Temporary Tables and Table Variables in SQL Server Easy Explaination

Temporary tables and Table variables, both have their own pros and cons. So it’s the developer who needs to decide which one to use and when according to the use cases and requirements.

In this post, we will learn the differences between Temporary Tables And Table Variables In SQL Server.

Temporary tables and Table variables Differences

⇒ Table variable (@table) is created in 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 the table variable is faster than the temporary table.

⇒ Temporary tables are allowed to CREATE INDEXes whereas, Table variables aren’t allowed to CREATE INDEX instead they can have indexed 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.

Temporary tables and Table variables Examples

In this section, we will explain about temporary tables and table variables in detail with the help of an SQL query example using SSMS.

Table Variable in SQL Server – Example

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

 DECLARE @TStudent TABLE  
 (  
    RollNo INT IDENTITY(1,1),  
    StudentID INT,  
    Name INT  
 )   
 --Insert data to Table variable @TStudent   
 INSERT INTO @TStudent(StudentID,Name)  
 SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
   
 --Select data from Table variable @TStudent  
 SELECT * FROM @TStudent  
   
 --Next batch  
 GO  
SELECT * FROM @TStudent --gives error  
DECLARE @TStudent TABLE  
 (  
    RollNo INT IDENTITY(1,1),  
    StudentID INT,  
    Name INT  
 )   
 --Insert data to Table variable @TStudent   
 INSERT INTO @TStudent(StudentID,Name)  
 SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
   
 --Select data from Table variable @TStudent  
 SELECT * FROM @TStudent  
   
 --Next batch  
 GO  
 SELECT * FROM @TStudent --gives an error  

Temporary Tables in SQL Server – Example

In SQL Server, based on the scope and behavior, temporary tables are of two types, local temp table(#) and global temp tables(##).

For the scope of this post as we are focusing on the differences between temporary tables and table variables so we will share the example of the local temp table.

CREATE TABLE #StudentTemp  
(  
    StudentID int,  
    Name varchar(50),   
    Address varchar(150)  
)  
GO  
INSERT INTO #StudentTemp VALUES ( 1, 'DotnetCrunch','Mumbai');  
GO  
SELECT * FROM #StudentTemp  
CREATE TABLE #StudentTemp  
(  
    StudentID int,  
    Name varchar(50),   
    Address varchar(150)  
)  
GO  
INSERT INTO #StudentTemp VALUES ( 1, 'Bazz','Delhi');  
GO  
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 the 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.

That’s all folks for now.

We hope that the difference between Temporary Tables and Table variables in SQL Server is clear.

You can also explore our posts on C# Language as well.

Happy Querying!