Post
Ask Question

Temporary Table Vs Temporary Variable in SQL Server

Temp Variables in SQL Server


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:

  1. Declare @My_vari TABLE  
  2. (  
  3.    IID int,  
  4.    Name Nvarchar(50),  
  5.    Salary Int ,  
  6.    City_Name Nvarchar(50)  
  7. )  
Temp Variables are also saved in the “tempdb” database similar to Temp Tables.

Insert data into Temp Variables
  1. Declare @My_var2 TABLE  
  2. (  
  3.    IID int,  
  4.    Name Nvarchar(50),  
  5.    Salary Int ,  
  6.    City_Name Nvarchar(50)  
  7. )  
  8.   
  9. Insert Into @My_var2   
  10. Select 1,'Pankaj',25000,'Alwar' Union ALL  
  11. Select 2,'Sandeep',28000,'Alwar' Union ALL /* Method 1*/  
  12. Select 3,'Sanjeev',27000,'Alwar'   
  13.   
  14. Insert Into @My_var2 Values(4,'Rahul',30000,'Jaipur')  
  15. Insert Into @My_var2 Values(5,'Naru',32000,'Jaipur') /* Method 2 */  
  16.   
  17. Insert Into @My_var2 /* Method 3*/  
  18. 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.

temp variables

Example 1
  1. Declare @My_var2 TABLE  
  2. (  
  3.    IID int,  
  4.    Name Nvarchar(50),  
  5.    Salary Int ,  
  6.    City_Name Nvarchar(50)  
  7. )  
  8.   
  9. Insert Into @My_var2   
  10. Select * from Employee  
  11.   
  12. Update @My_var2 Set name='Pankaj Kumar Choudhary'   
  13. where IID >2  
  14.   
  15. Delete From @My_var2 Where IID=1  
  16. Select * from @My_var2  
Output

SELECT INTO command

We can implement all DML andDQL commands for Temp Variables.

Example 2
  1. Declare @My_var2 TABLE  
  2. (  
  3.    IID int Unique ,  
  4.    Name Nvarchar(50) primary Key,  
  5.    Salary Int Check (Salary<50000),  
  6.    City_Name Nvarchar(50) Not Null Default('Alwar')  
  7. )  
  8.   
  9. Insert Into @My_var2   
  10. Select * from Employee  
  11.   
  12.   
  13. Select * from @My_var2  
Output

implement all DML

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

  1. 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.

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

  3. 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.

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

  5. 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.

  6. 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.

  7. Rollback
    Let us see an example.
    1. Declare @My_var2 TABLE  
    2. (  
    3.    IID int Unique ,  
    4.    Name Nvarchar(50) primary Key,  
    5.    Salary Int Check (Salary<50000),  
    6.    City_Name Nvarchar(50) Not Null Default('Alwar')  
    7. )  
    8.   
    9. INSERT INTO @My_var2  
    10. Select 1,'Pankaj',25000,'Alwar' Union ALL  
    11. Select 2,'Sandeep',28000,'Alwar' Union ALL /* Method 1*/  
    12. Select 3,'Sanjeev',27000,'Alwar'   
    13.   
    14. Begin Transaction My_Trans  
    15. DELETE FROM @My_var2 WHERE IID=2  
    16. Begin Transaction My_Trans  
    17. Rollback Transaction My_Trans  
    18.   
    19. SELECT * FROM @My_var2  
    Output

    Rollback

    Now another example.
    1. CREATE TABLE #My_var2   
    2. (  
    3.    IID int Unique ,  
    4.    Name Nvarchar(50) primary Key,  
    5.    Salary Int Check (Salary<50000),  
    6.    City_Name Nvarchar(50) Not Null Default('Alwar')  
    7. )  
    8.   
    9. INSERT INTO #My_var2  
    10. Select 1,'Pankaj',25000,'Alwar' Union ALL  
    11. Select 2,'Sandeep',28000,'Alwar' Union ALL   
    12. Select 3,'Sanjeev',27000,'Alwar'   
    13.   
    14. Begin Transaction My_Trans  
    15. DELETE FROM #My_var2 WHERE #My_var2.IID=2  
    16. Begin Transaction My_Trans  
    17. Rollback Transaction My_Trans  
    18.   
    19. SELECT * FROM #My_var2  
    Output

    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.


Similar Articles