Table Variable in SQL Server

The Table Variable in SQL Server stores a set of records like SQL tables. And Table Variables are the best alternative to Temp Tables. Like Local Variables, Table variable scope limited to User Defined Functions or Stored procedures.

The SQL Server Table variable is very fast when compared to temporary tables, and it is recommended to use this for less amount of data. The syntax of the Table variable is similar to the Create Table Statement.

-- SQL Server Table variable Syntax
DECLARE @Table_Variable_Name TABLE
(
    Column_Name1 Data_Type(Size) [NULL | NOT NULL],
    Column_Name2 Data_Type(Size) [NULL | NOT NULL],
     …
    Column_NameN Data_Type(Size) [NULL | NOT NULL]
);

Create Sql Server Table Variable Example

Let us see how to Create a table variable in SQL Server, How to insert new records into it. And, how to select records from the table variable.

TIP: You cannot use a table variable as either Input or an Output Parameter.

-- Creating SQL Server Table Variable
DECLARE @TempTableVariable TABLE
(
  [CustomerKey] [int] IDENTITY(1,1) NOT NULL,
  [FirstName] [varchar](50) NULL,
  [LastName] [varchar](50) NULL,
  [BirthDate] [date] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Yearly Income] [money] NULL,
  [Profession] [nvarchar](100) NULL
)
-- Inserting Values in SQL Table Variable
INSERT INTO @TempTableVariable 
VALUES ('Imran', 'Khan', '10-08-1985', '[email protected]', 15900, 'Skilled Professional')
      ,('Doe', 'Lara', '10-08-1985', '[email protected]', 15000, 'Management')
      ,('Ramesh', 'Kumar', '10-08-1985', '[email protected]', 65000, 'Professional')

-- Selecting Values from SQL Server Table Variable
SELECT * FROM @TempTableVariable

OUTPUT

As you can see from the above screenshot, we are executing all the statements together. Let me show you what happens when we run them independently.

There won’t be any problem to declare a table variable

OUTPUT

NOTE: You don’t need any DELETE or DROP statement to drop the table variable. Database Engine will automatically delete when it exits from the scope

I hope you understood the scope of a table variable. Please refer Temp Tables, User Defined Functions, Stored procedures, and Create Table.