Temporary Table Vs Temporary Variable in SQL Server

Temp Variables

Temp Variables are also used for holding data temporarily just like a temp table. Temp Variables are created using a “DECLARE” statement and are assigned values using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a specific batch of query execution. It is dropped once it comes out of the batch.

Temp Variables are also created in the Tempdb database but not the memory.

Temp Variables are similar to Temp Tables in some cases but there are also some differences between them. We will also explain this topic later in this article.

Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement as you can see in the following example:

Declare @My_vari TABLE
(
   IID int,
   Name Nvarchar(50),
   Salary Int ,
   City_Name Nvarchar(50)
)

Temp Variables are also saved in the “tempdb” database similar to Temp Tables.

Insert data into Temp Variables

Declare @My_var2 TABLE
(
   IID int,
   Name Nvarchar(50),
   Salary Int ,
   City_Name Nvarchar(50)
)

Insert Into @My_var2
Select 1,'Pankaj',25000,'Alwar' Union ALL
Select 2,'Sandeep',28000,'Alwar' Union ALL /* Method 1*/
Select 3,'Sanjeev',27000,'Alwar'

Insert Into @My_var2 Values(4,'Rahul',30000,'Jaipur')
Insert Into @My_var2 Values(5,'Naru',32000,'Jaipur') /* Method 2 */

Insert Into @My_var2 /* Method 3*/
Select * from Employee

This example illustrates that we can use all the same methods of insertion that are used for tables but we cannot use a “SELECT INTO” statement for Temp Variables. The best we can do is to create it first, then insert into it.



Example 1

Declare @My_var2 TABLE
(
   IID int,
   Name Nvarchar(50),
   Salary Int ,
   City_Name Nvarchar(50)
)

Insert Into @My_var2
Select * from Employee

Update @My_var2 Set name='Pankaj Kumar Choudhary'
where IID >2

Delete From @My_var2 Where IID=1
Select * from @My_var2

Output



We can implement all DML andDQL commands for Temp Variables.

Example 2

Declare @My_var2 TABLE
(
   IID int Unique ,
   Name Nvarchar(50) primary Key,
   Salary Int Check (Salary<50000),
   City_Name Nvarchar(50) Not Null Default('Alwar')
)

Insert Into @My_var2
Select * from Employee


Select * from @My_var2

Output



Constraints are an excellent way to ensure that the data in a table meets specific requirements and you can use constraints with table variables.

The following are the limitations of Temp Variables: 

1. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
   
   
2. The variable will not exist after the procedure exits. There will be no table to clean up with a DROP statement.
   
   
3. We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY and UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.

Temporary Table Vs Temporary Variables

Scope

The Scope of the Temp Variables are limited to the current batch and current Stored Procedure, but the scope of a Temp Table is wider than for Temp Variables. Local temporary tables are temporary tables that are available only to the session that created them and global temporary tables are temporary tables that are available to all sessions and all users.

Creation

We can declare Temp Variables only using a Declare statement but Temp Tables can be created using Create Table and Select Into commands.

Drop and Truncate Command

We cannot drop a Temp variable but Temp Tables can be dropped using a Drop Command. We cannot use the truncate command for Temp Variables but we can do it for Temp Tables.

Table name

The Name of a temp variable can have a maximum of 128 characters and a Temp Table can have 116 characters.

Constraint

Temp Tables and Temp Variables both support unique key, primary key, check constraints, Not null and default constraints but a Temp Variable doesn't support Foreign Keys.

Dynamic SQL

Temp Variables must declare a table variable inside the dynamic SQL but a Temp Table can use Temporary Tables created prior to calling the dynamic SQL.

Rollback

Let us see an example.


Declare @My_var2 TABLE
(
   IID int Unique ,
   Name Nvarchar(50) primary Key,
   Salary Int Check (Salary<50000),
   City_Name Nvarchar(50) Not Null Default('Alwar')
)

INSERT INTO @My_var2
Select 1,'Pankaj',25000,'Alwar' Union ALL
Select 2,'Sandeep',28000,'Alwar' Union ALL /* Method 1*/
Select 3,'Sanjeev',27000,'Alwar'

Begin Transaction My_Trans
DELETE FROM @My_var2 WHERE IID=2
Begin Transaction My_Trans
Rollback Transaction My_Trans

SELECT * FROM @My_var2

Output



Now another example.


CREATE TABLE #My_var2
(
   IID int Unique ,
   Name Nvarchar(50) primary Key,
   Salary Int Check (Salary<50000),
   City_Name Nvarchar(50) Not Null Default('Alwar')
)

INSERT INTO #My_var2
Select 1,'Pankaj',25000,'Alwar' Union ALL
Select 2,'Sandeep',28000,'Alwar' Union ALL
Select 3,'Sanjeev',27000,'Alwar'

Begin Transaction My_Trans
DELETE FROM #My_var2 WHERE #My_var2.IID=2
Begin Transaction My_Trans
Rollback Transaction My_Trans

SELECT * FROM #My_var2

Output

  

The preceding example shows that Temp Variables don't support ROLLBACK but Temp Tables support ROLLBACK.

So Temp Variables should be used over a Temp Table when the data is less for manipulation.