Rajendra Gupta
SQL Table Variables

An overview of the SQL table variable

October 16, 2019 by

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:

A SQL temporary table uses following process.

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

SQL Temporary table

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:

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:

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

SQL Table Variables

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


Dropping a SQL Table Variables

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

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

table variable storage

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:

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

table variable storage location

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:

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

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

explicit transaction in a 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:

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

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

It retrieves the records from UDF:

User-defined functions (UDF) and Table variable

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:

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
  • 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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
300 Views