Post
Ask Question

Table Variable in SQL Server

Just like temporary tables, we have table variables in SQL. It is an alternative to temporary tables. If you’re using temporary tables and don’t need transactions on those tables and want better performance then use table variables  instead of temporary tables.

Syntax

  1. DECLARE @Student TABLE  
  2. (  
  3.    StudentIdint NOT NULL,  
  4.    StudentName varchar,  
  5.    Marks int  
  6. )  
We can insert records in the table variables just like temporary tables. Select, Update, and delete also works with the table variable. In table variables the constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY constraints are not allowed.

Example of Table variable.

Copy the below script and execute in the SQL Server.
  1. DECLARE @Student TABLE  
  2. (  
  3.    StudentIdint NOT NULL,  
  4.    StudentNamevarchar(20),  
  5.    Marks int  
  6. )  
  7.    
  8.    
  9. Insert Into @Student (StudentID,StudentName,Marks) Values (1,'Nitin Tyagi',200)  
  10. Insert Into @Student (StudentID,StudentName,Marks) Values (2,'Amar Singh',400)  
  11. Insert Into @Student (StudentID,StudentName,Marks) Values (3,'Vicky',300)  
  12.    
  13. Select  * from @Student   
Following is the output we get when the preceding script is executed.



When we create a table variable it only resides in memory which means it’s much faster. A table variable goes out of scope immediately after the batch ends just like regular variables go out of scope. This means we don’t have to explicitly drop them at the end of scripts.

Table variable can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. We can also return a table variable from a user-defined function.

This is how we can use table variables in SQL Server.