Learn about SQL Table Variable?

Introduction

Like the temp table in SQL Server, the table variable is a specific kind of local variable that aids with temporary data storage. Unlike temp or normal tables, which have no restrictions, local variables have constraints. However, the table variable offers all the properties of the local variable.

SQL Table Syntax

Declaring a table variable uses the syntax shown below:

DECLARE @TableName TABLE
(
 column1 DATATYPE, 
 column2 DATATYPE, 
 ...
 columnN DATATYPE
)

Similar to local variables, we must start the DECLARE declaration in order to declare a table variable. The at(@) sign must appear at the beginning of the local variable name. This variable's table-specific nature is indicated by the TABLE keyword. The table variable's column names and datatypes must be defined in SQL Server after the TABLE keyword.

Scope of Table variables

Table variables are out of scope at the conclusion of the batch, much as local variables.

A table variable that is defined inside of a user-defined function or stored procedure will be eliminated when the function or stored procedure ends.

The months and their abbreviations will be inserted into a table variable that will be declared in the example that follows:

DECLARE @Months TABLE
(
	MonthId INT,
	[Name] VARCHAR(20), 
	ShortName VARCHAR(3),
	Season VARCHAR(10)
)

INSERT INTO @Months
VALUES (1, 'January', 'Jan', 'Winter')
, (2, 'February', 'Feb', 'Winter')
, (3, 'March', 'Mar', 'Summer')
, (4, 'April', 'Apr', 'Summer')
, (5, 'May', 'May', 'Summer')
, (6, 'June', 'Jun', 'Rainy')
, (7, 'July', 'Jul', 'Rainy')
, (8, 'August', 'Aug', 'Rainy')
, (9, 'September', 'Sep', 'Rainy')
, (10, 'October', 'Oct', 'Autumn')
, (11, 'November', 'Nov', 'Autumn')
, (12, 'December', 'Dec', 'Winter')

SELECT * FROM @Months

We can edit and remove the data that is stored in the table variables simultaneously. The query that follows updates and removes rows:

DECLARE @Months TABLE
(
	MonthId INT,
	[Name] VARCHAR(20), 
	ShortName VARCHAR(3),
	Season VARCHAR(10)
)

INSERT INTO @Months
VALUES (1, 'January', 'Jan', 'Winter')
, (2, 'February', 'Feb', 'Winter')
, (3, 'March', 'Mar', 'Summer')
, (4, 'April', 'Apr', 'Summer')
, (5, 'May', 'May', 'Summer')
, (6, 'June', 'Jun', 'Rainy')
, (7, 'July', 'Jul', 'Rainy')
, (8, 'August', 'Aug', 'Rainy')
, (9, 'September', 'Sep', 'Rainy')
, (10, 'October', 'Oct', 'Autumn')
, (11, 'November', 'Nov', 'Autumn')
, (12, 'December', 'Dec', 'Winter')

DELETE @Months WHERE Season = 'Summer'

UPDATE @Months SET [Name] = 'November is cool' WHERE MonthId = 11

SELECT * FROM @Months

Table variable restrictions

During the declaration, you must first define the table variable's structure. The structure of the table variables cannot be changed after they are stated, in contrast to a regular or temporary table.

Secondly, statistics assist the query optimizer in creating a well-thought-out execution strategy for the query. Regretfully, statistics are absent from table variables. As a result, you ought to save a limited number of rows using table variables.

Thirdly, unlike other data types, the table variable cannot be used as an input or output parameter. However, a user-defined function can return a table variable.

Fourth, table variables cannot have non-clustered indexes created for them. Nevertheless, memory-optimized table variables are now possible with SQL Server 2014 because to the new In-Memory OLTP feature, which enables you to include non-clustered indexes in the table variable declaration.

Fifth, you must alias the table in order to run the query if you are utilizing a table variable with a join. As an illustration:

DECLARE @Seasons TABLE
(
	SeasonId TINYINT,
	Season VARCHAR(10)
)

INSERT INTO @Seasons
VALUES (1, 'Winter'), (2, 'Summer'), (3, 'Rainy'), (4, 'Autumn')

DECLARE @Months TABLE
(
	MonthId INT,
	[Name] VARCHAR(20), 
	ShortName VARCHAR(3),
	Season TINYINT
)

INSERT INTO @Months
VALUES (1, 'January', 'Jan', 1)
, (2, 'February', 'Feb', 1)
, (3, 'March', 'Mar', 2)
, (4, 'April', 'Apr', 2)
, (5, 'May', 'May', 2)
, (6, 'June', 'Jun', 3)
, (7, 'July', 'Jul', 3)
, (8, 'August', 'Aug', 3)
, (9, 'September', 'Sep', 3)
, (10, 'October', 'Oct', 4)
, (11, 'November', 'Nov', 4)
, (12, 'December', 'Dec', 1)

SELECT M.Name, M.ShortName, S.Season
FROM @Months AS M
INNER JOIN @Seasons AS S ON S.SeasonId = M.Season

Where are the table variables kept in storage?

Table variables are kept in the tempdb database, is the response to this query. We highlight this because, although it is completely incorrect, there are situations when the response to this query is that the table variable is kept in memory. We need to address one confusion regarding the table variables before we can demonstrate the answer to this query. The table variables have a lifecycle that begins at the declaration point and ends at the batch's conclusion. Consequently, at the conclusion of the batch, SQL Server automatically drops the table variable:

DECLARE @Seasons TABLE
(
	SeasonId TINYINT,
	Season VARCHAR(10)
)

SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'Season%';
    
GO
SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'Season%';

The preceding query yields two result sets, as you can see. The declared table variable's column names and data types are contained in the ResultSet-1, whereas there is no data in the ResultSet-2. The initial INFORMATION_SCHEMA.COLUMNS view and table variable performed in the same batch is the reason for this scenario; this allows us to retrieve the @Seasons table variable's data from the tempdb database. The GO statement stops the batch, which ends the life-cycle of the @Seasons table variable, hence the second query was unable to produce any information about the @Seasons. We demonstrated where SQL Server stores table variables in this section.

How may the table variables be used with constraints?

Database elements called constraints guarantee the integrity of the data. Table factors enable us to establish the subsequent limitations:

• Primary key
• Unique
• Null
• Check

We will use every kind of constraint on the table variable with ease in the example that follows:

DECLARE @Emp TABLE
(
	Id TINYINT PRIMARY KEY,
	[Name] VARCHAR(20) NOT NULL,
	Age INT CHECK (Age > 18)
)

INSERT INTO @Emp
VALUES (1, 'Jaimin', 21)

SELECT * FROM @Emp

DECLARE @Emp TABLE
(
	Id TINYINT PRIMARY KEY,
	[Name] VARCHAR(20) NOT NULL,
	Age INT CHECK (Age > 18)
)

INSERT INTO @Emp
VALUES (1, 'Jaimin', 21)
, (2, 'Dwisha', 7)

SELECT * FROM @Emp

In the above example, the employee will not insert due to the age check constraint.

However, table variables cannot be used with Foreign Key constraints. The third limitation is that in order to avoid errors, we must define the restrictions at the time of table variable declaration. This restriction will result in an error for the following query, for instance. After the table variable is declared, we are unable to change the table structure:

DECLARE @Emp TABLE
(
	Id TINYINT NOT NULL
)

ALTER TABLE @Emp
ADD CONSTRAINT PK_Id PRIMARY KEY (Id)

SQL Server transactions and table variables

The smallest logical unit used by SQL Server to assist in managing CRUD (insert, select, update, and delete) operations is called a transaction. The BEGIN TRAN command initiates explicit transactions, which can be finished with the COMMIT or ROLLBACK instructions. We will now run the following query and examine the outcome:

DECLARE @Emp TABLE
(
	Id TINYINT NOT NULL PRIMARY KEY,
	[Name] VARCHAR(20) NOT NULL,
	Age INT CHECK (Age > 18)
)
BEGIN TRAN

INSERT INTO @Emp
VALUES (1, 'Jaimin', 21)

ROLLBACK TRAN

SELECT * FROM @Emp

Table element Explicit transactions are not used to manage CRUD operations. Consequently, ROLLBACK TRAN is unable to remove the updated information for the table variables.

We learned the new technique and evolved together.

Happy coding :)