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.

Sql Server RANK

Rank function provider rank of rows of a particular set over a partition. The partition can be a full set of data or grouping of data .Rank always return bigint.

Syntax :

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

        id              INT NOT NULL,

        name            VARCHAR (500) NOT NULL,

        age             INT NOT NULL,

        adress          VARCHAR (500) NOT NULL,

        date_of_birth   DATETIME NOT NULL,

        height          DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'34,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'35,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'36,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'34,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'35,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'36,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'34,student street','11/08/2018',16

Here is the example of RANK function with partition.

SELECT name,

age,

RANK() OVER (PARTITION BY age ORDER BY date_of_birth DESC) AS DRank

FROM student 

output  

name            age       DRank

John9    12    1
John5    12    2
John3    12    3
John1    12    4
John6    13    1
John4    13    2
John2    13    3
John8    14    1
John10   15    1
John7    15    2
John11   16    1

Here is rank over the age , separated by partition. 

Here is the example of RANK function without partition.

SELECT name,

age,

RANK() OVER (ORDER BY date_of_birth DESC) AS DRank

FROM student

output  

name            age       DRank

John11    16    1
John10    15    2
John9    12    3
John8    14    4
John7    15    5
John6    13    6
John5    12    7
John4    13    8
John3    12    9
John2    13    10
John1    12    11