An overview of the SQL table variable

This article explores the SQL Table variables and their usage using different examples.

Introduction

We use many intermediate tables to perform data manipulation, computation, and data sorting from multiple sources. It is beneficial to use temporary tables instead of a regular table and drop them later. Usually, we define SQL temporary tables using the # symbol:

CREATE TABLE #temp(DBname VARCHAR(20));

INSERT INTO #temp

SELECT name

FROM sys.sysdatabases;

SELECT *

FROM #temp;

DROP TABLE #temp;

A SQL temporary table uses following process.

Define
Use (Insert, update, delete, select)
Drop

In this article, we will look at an alternative way of storing temporary data, i.e. SQL table variables. Let’s take an overview of it along with multiple examples.

Overview of SQL table variables

The table variable is a special data type that can be used to store temporary data similar to a temporary table. The syntax for the table variable looks similar to defining a new table using the CREATE TABLE statement:

DECLARE @Products TABLE

(ProductID INT,

ProductName VARCHAR(30)

);

In the above query, we specified a table variable using the DECLARE and TABLE keyword. The table variable name must start with the @ symbol. We also define table columns, data types, constraint similar to a regular table.

Scope of SQL table variable

The table variable scope is within the batch. We can define a table variable inside a stored procedure and function as well. In this case, the table variable scope is within the stored procedure and function. We cannot use it outside the scope of the batch, stored procedure or function.

Insert data into a table variable

We can insert data into a table variable similar to a regular table:

DECLARE @Products TABLE

(ProductID INT,

ProductName VARCHAR(30)

);

INSERT INTO @Products

VALUES

(1,

'Carpet'

);

You can retrieve a record from a table variable using the Select statement:

We cannot drop the table variable using the DROP Table statement. If you try to drop it, you get incorrect syntax message:

We do not require dropping the table variable. As mentioned earlier, the scope of the table variable is within the batch. The scope of it lasts at the end of the batch or procedure.

The storage location of SQL table variable

Let’s think of a few questions:

What is the storage location of a table variable?
Is it created in the source database in which we execute the script?

Most people are confused about the table variable location and think that it is stored in memory. Let’s check it out.

We can use sys.tables for listing tables in the tempdb database. Let’s execute the following query and do the following:

Check the existing tables in tempdb
Declare a table variable
Check the tables in tempdb again

SELECT *

FROM TEMPDB.sys.tables;

DECLARE @Products TABLE

(ProductID INT,

ProductName VARCHAR(30)

);

SELECT *

FROM TEMPDB.sys.tables;

In the output, we did not get any existing table before and after declaring a table variable:

Does it mean that the table variable is not stored in the tempdb database? No, it is not valid. You can note that we use Go statement after each step to finish the batch before starting the subsequent batch. Table variable scope finishes at the end of the batch, and SQL Server automatically drops it.

Let’s run the following query. In the modified query, we removed the Go statement after declaring a table variable and checking the existence of it in the tempdb:

SELECT *

FROM TEMPDB.sys.tables;

DECLARE @Products TABLE

(ProductID INT,

ProductName VARCHAR(30)

);

SELECT *

FROM TEMPDB.sys.tables;

Now, in the output, it shows the table variable in the tempdb database:

Table variable and explicit transaction

We cannot use the table variable in the explicit transaction, it does not return any error message, but it skips the transaction.

In the following query, we execute the query in four steps:

Declare a table variable
Starts a transaction using the BEGIN TRAN statement
Insert record into the table variable
Rollback transaction using ROLLBACK TRAN
Verify whether the record exists in the table variable or not. It should not exist because we performed rollback in step 4

DECLARE @TableVariableA TABLE(Name VARCHAR(30));

BEGIN TRAN;

INSERT INTO @TableVariableA

VALUES('SQL Server');

ROLLBACK TRAN;

SELECT *

FROM @TableVariableA;

In the output, we can verify that ROLLBACK TRAN did not perform a rollback of data from the table variable:

If we require explicit transactions, we can use the temporary tables. The explicit transaction works on temporary tables.

User-defined functions (UDF) and table variable

We can define and use table variables in the user-defined functions as well. Execute the following query:

Define a user-defined function using CREATE FUNCTION Statement
Define a table variable and define columns for it
Define UDF activity in the BEGIN…END statement
Return the variable value

CREATE FUNCTION TableVariableWithinAFunction()

RETURNS @TableVariable TABLE(Name VARCHAR(50))

BEGIN

INSERT INTO @TableVariable

SELECT FirstName

FROM [DemoSQL].[dbo].[Employee];

RETURN;

END;

Later, we can use a UDF function to retrieve the records:

1	Select * from TableVariableWithinAFunction()

It retrieves the records from UDF:

This example showed that we could use table variables in a user-defined function as well. We cannot use temporary tables inside a user-defined function.

Indexes and table variable

Table variables are a particular type of data types. We can use these table variables similar to the user table to hold temporary data.

Q: Is it possible to add indexes on the table variables?

A: No, we cannot define indexes on the table variables.

Q: If we cannot define indexes on table variables, do we have any alternatives for it?

A: Yes, indeed, we cannot define index in the table variables, but we can define primary and unique key constraints on the table variables:

DECLARE @Customer TABLE

(CustomerId INT

PRIMARY KEY NONCLUSTERED,

CustomerName NVARCHAR(30) UNIQUE CLUSTERED(CustomerName)

);

We cannot define an explicit clustered and non-clustered index on the table variable. Primary key and unique key constraints automatically create the internal indexes on it. You can use these constraints to unique define rows in an index as well.

Can we modify a SQL table variable structure after declaration?

A: No, we cannot alter a table variable definition after the declaration. Suppose you define a table variable for holding the Varchar data type of length 50. Later, our requirement changes, and we want to modify it for length 100.

We cannot alter a table variable structure. We can define another table variable. We can also modify the definition of an existing table variable.

Conclusion

In this article, we explored the SQL table variables and their usage for storing temporary data. We also compared it with the temporary tables. Let’s have a quick summary of what we have learned:

Temporary table

SQL table variable

Syntax

We use the following format for defining a temporary table:

Define
Use
Drop

1 2	CREATE TABLE #Employee (Id INT, Name VARCHAR(50))

We define a temporary table using the hash sign.

In Table variable, we use the following format:

Define
Use

We cannot drop a table variable using an explicit drop statement.

Storage

It is stored in the tempdb system database.

The storage for the table variable is also in the tempdb database.

Transactions

We can use temporary tables in explicit transactions as well.

Table variables cannot be used in explicit transactions.

User-defined function

We cannot use it in the user-defined functions.

We can use table variables in the UDF.

Indexes

We can define explicit indexes on the temporary tables.

We cannot define explicit indexes on table variables. We can use primary and unique key constraints.

Scope

Scope of a temporary table is local and global as defined while creating it.

Scope of the table variable is within the batch. We cannot use it outside the batch.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023

Introduction

Overview of SQL table variables

Scope of SQL table variable

Insert data into a table variable

The storage location of SQL table variable

Table variable and explicit transaction

User-defined functions (UDF) and table variable

Indexes and table variable

Conclusion

Related posts: