SQL Server Table Variable

Table variable refers to a local variable that allows you to hold data temporarily, similar to temporary tables. Table variables are not very different from other local variables.

In this guide, you’ll understand how to work with table variables in SQL Server and look at various examples of working with table variables.

The Basics

Table variables enable you to hold rows of data similar to temp tables in SQL Server. You create a table variable by declaring it using the SQL Server variable declaration statement. This means the variable’s name starts with an @ sign similar to other local variables. Unlike other local variables, a table variable has a type of table.

Once the variable is declared, you can define columns, functions, stored procedures, and more.

Like other local variables, table variables have a scope limited to the end of a batch. For example, if they define a table variable inside a function, the table variable is only available inside that function.

Declaring Table Variables

To declare a table variable, follow the syntax as shown below:

DECLARE @var_name TABLE (
column_1,
column_2,
...
column_n
);

As mentioned, we pass the variable’s name between the SQL Server declare and table keywords. Inside the parenthesis, you can define the structure of the table variable. These include column lists, data types, size, constraints, and more.

SQL Server Table Variables Examples

The following examples illustrate how to work with SQL Server table variables.

Example 1: Declare Table Variable

The following SQL query declares a simple table variable with four columns.

DECLARE @simple_table_var TABLE (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE,
subscription money
);

Example 2: Insert Data into the Table variable

Once the table variable is declared, we can insert records using the insert statement.

INSERT INTO @simple_table_var
VALUES ('user1', '[email protected]', 9.99),
('user2', '[email protected]', 15.99),
('user3', '[email protected]', 2.99),
('user4', '[email protected]', 29.99),
('user5', '[email protected]', 0);

Example 3: Query Data from Table Variables

A table variable behaves like a temporary table in SQL Server. Hence, we can query the records stored using a select statement.

SELECT * FROM @simple_table_var;

The query above should return the data stored in the table variable.

Keep in mind that table variables are only available within a specific batch. Hence, you need to execute the entire query from the declare statement to the select statement as:

DECLARE @simple_table_var TABLE (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE,
subscription money
);
INSERT INTO @simple_table_var
VALUES ('user1', '[email protected]', 9.99),
('user2', '[email protected]', 15.99),
('user3', '[email protected]', 2.99),
('user4', '[email protected]', 29.99),
('user5', '[email protected]', 0);
SELECT * FROM @simple_table_var;

Otherwise, SQL Server will return an error as:

Table Variables: Key Points

There are various characteristics and features of table variables you should know. These include:

You cannot alter the structure of a table variable once defined.
Table variables do not provide statistics.
For joins using a table variable, you need to alias the table.
Table variables require fewer resources compared to temp tables.
SQL Server stores table variables in tempdb.

Conclusion

In this guide, we covered the basics of working with table variables. We explored how to declare a table variable, insert and query data stored in them.

Thanks for reading!