SQL Server Table Variable

Table variable is used to store record in particular scope. A table variable can be used in database functions , store procedure , Trigger etc. 

A table variable can performed data manipulation operation like insert , update , delete. A table variable can be used for joining purpose also (INNER JOIN ,OUTER JOIN,LEFT JOIN, RIGHT JOIN). 

Below is the syntax of table variable. 

DECLARE @VARIABLE_NAME TABLE

(

     COLUMN_NAME DATATYPE CONSTRAIN,

     COLUMN_NAME DATATYPE CONSTRAIN,

     COLUMN_NAME DATATYPE CONSTRAIN,

     ...............................

     ...............................

)

 Below is the example of table variable.

DECLARE @MyTable TABLE

(

      StudentID int IDENTITY,

      StudenNAME VARCHAR(50),

      ROLL INT

)

Below is example of table variable insert ,update and delete.

insert 

 

DECLARE @MyTable TABLE

(

    StudentID int IDENTITY,

    StudenNAME VARCHAR(50),

    ROLL INT

)

INSERT INTO @MyTable(StudenNAME,ROLL)

VALUES('John',32)

UPDATE

DECLARE @MyTable TABLE

(

      StudentID int IDENTITY,

      StudenNAME VARCHAR(50),

      ROLL INT

)

INSERT INTO @MyTable(StudenNAME,ROLL)

SELECT 'John',32

UNION ALL

SELECT 'John2',33

UPDATE @MyTable

SET StudenNAME='John3'

WHERE ROLL=32

SELECT * FROM @MyTable

StudentID         StudenNAME   ROLL

1           John3      32
2           John2      33

Below is the example of table variable is used in a function.

CREATE FUNCTION dbo.GetStudentList()

RETURNS int

AS

BEGIN

DECLARE @c INT

      DECLARE @MyTable TABLE

     (

            StudentID int IDENTITY,

            StudenNAME VARCHAR(50),

           ROLL INT

   )

INSERT INTO @MyTable(StudenNAME,ROLL)

SELECT 'John',32

UNION ALL

SELECT 'John2',33

SELECT @c=COUNT(1) FROM @MyTable

RETURN @c;

END;

GO

SELECT dbo.GetStudentList()

Output :2


Table variable is widely used for its flexibility and performance. Table variable can be used as a input parameter of function also. 

Table variable scope is limited, you cannot access table variables out of its scope. If you use table variable in a function or   stored procedure, after execution of function or stored procedure , you will not get the table variable. If you use table variable in a query, you will not get table variable after the execution of query. 


Table variable have wide functionality, you can use identity, check constant, default value but you will not able to create index as it is not a physical table.

Here is the example of default and identity is used in table variables.

DECLARE @MyTable TABLE

(

      StudentID int IDENTITY,

      StudenNAME VARCHAR(50),

      ROLL INT,

      School VARCHAR(50) DEFAULT 'My Favourite School'

)

Table variables is faster than temporary table as  Table variable use least overhead. But you cannot perform transaction management with table variables.

Variable is generally used for small data for temporary storage purpose. SQL server temporary table has no limit of size, but for large data temp table is used.