How to create a table variable in SQL Server

In this very brief tutorial, we’ll discuss how you can create a table variable in Microsoft SQL Server. Knowing how to create a SQL Server table variable can be very useful when querying data in a database. It is very simple, and there are only a few rules you need to know.

Without further ado,

How to create a SQL Server table variable:

Let’s start with an example of how to create a regular table. We’ll create a simple Products table:

CREATE TABLE Products
(
ProdID INT INDEX idx_ProdID CLUSTERED,
ProductName VARCHAR(20) INDEX idx_ProductName NONCLUSTERED,
ProductPrice DECIMAL(5,2),
InStock BIT DEFAULT(1)
)

To create this table as a table variable instead, we basically just replace CREATE TABLE Products with DECLARE @Products TABLE . Like so:

DECLARE @Products TABLE 
(
ProdID INT INDEX idx_ProdID CLUSTERED,
ProductName VARCHAR(20) INDEX idx_ProductName NONCLUSTERED,
ProductPrice DECIMAL(5,2),
InStock BIT DEFAULT(1)
)

To further understand this, let’s revisit how to create a regular variable like an INT variable:

DECLARE @someInteger INT

When creating a regular variable, we use the ‘DECLARE‘ keyword followed by the name of the variable we want to create with the ‘@‘ symbol in front of it, followed by the data type.

When it comes to a table variable, we still use the ‘DECLARE‘ keyword followed by the name of the variable we want to create with the ‘@‘ symbol in front of it, followed by the data type which would be simply ‘TABLE‘.

Notice the content of the table is unchanged. We still outline the columns and their data types. We can even outline things like clustered and nonclustered indexes and constraints on the columns in the table variable like in our example.

You can do many of the things you would do to a regular table to a table variable

Once we have this table variable created, we can do nearly everything we would do to a normal table. We’ll add some rows to the table:

One common trick you can use for populating a table variable is to use the INSERT INTO…SELECT clause. For example, the following query will insert all the content from my regular Products table into our variable @Products table:

Of course, you can also do UPDATES and DELETES to the rows in a table variable, too.

Some rules you need to know

There are some rules you need to know when using table variables.

1) The scope of a table variable is very short

A table variable is not created as an object in the SQL Server, nor is it stored in tempdb. Table variables purely exist in memory, and they fall out of scope when the batch ends.

For example, let’s revisit that last INSERT INTO…SELECT statement:

Notice I have all the code highlighted, which means it all runs in the same batch. If I try to run the SELECT statement all by itself, it won’t work:

SQL Server has no idea what @Products is.

This is actually true for any variable. The variable must be declared in the same batch it’s being used in, and the variable falls out of scope when the batch ends.

Learn more about batches in the SQL Server GO tutorial:

SQL Server GO: Explained with Examples

2) ROLLBACK does not undo work done to a table variable

Whenever we need to ROLLBACK a transaction, this normally means any work that was performed within the transaction will be undone. However, when it comes to table variables in a transaction, any work ran against them is not undone by a ROLLBACK.

As a very simple example, let’s insert rows into our table variable within a transaction, then immediately ROLLBACK the transaction:

From the SELECT statement at the end, we can see the data is still in the table variable. The work done to the table variable was simply not undone by the ROLLBACK.

This is another thing that is true for any variable.

3) Table variables behave much like regular tables

I said this before, but it’s worth repeating. Many of the things you can do to a regular table can also be done to a table variable. You can do things like:

• Add indexes
• Create constraints
• Use the IDENTITY property
• Add primary keys
• etc!

The biggest difference, as discussed, is that the scope is very short!

Next Steps:

Leave a comment if you found this tutorial helpful!

You should read up on other common SQL Server questions, such as:

• How to change a table name in SQL Server

• UNION and UNION ALL: What’s the difference?

• SQL Server NULL: Are you making these 7 mistakes?

Knowing these little tid-bits about SQL Server is what will make you stand out from your peers.

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, leave a comment or even better, send me an email!