Table Variable in SQL Server

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

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

DECLARE @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 the Server, How to insert new records into it. And how to select records from it.

TIP: You cannot use it as either an Input or an Output Parameter.

DECLARE @Records 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
INSERT INTO @Records 
VALUES ('Imran', 'Khan', '10-08-1985', 'abc@abc.com', 15900, 'Skilled Professional')
      ,('Doe', 'Lara', '10-08-1985', 'xyz@abc.com', 15000, 'Management')
      ,('Ramesh', 'Kumar', '10-08-1985', 'mnop@abc.com', 65000, 'Professional')

-- Selecting Values
SELECT * FROM @Records