Can we create indexes in table variables? YES WE CAN!

It is absolutely possible to create indexes within a table variable in Microsoft SQL Server.

The key is to add the indexes as part of the table variable definition

Take a look at this regular table definition (which does not have any indexes placed on it yet):

CREATE TABLE Books
(
BookID INT,
Author VARCHAR(30),
Title VARCHAR(30),
Pages INT
)

Understand that one way we can add indexes to a table is by including the index syntax within the definition of the table. For example, here is how we would add a clustered index to the BookID column:

CREATE TABLE Books
(
BookID INT INDEX idx_BookID CLUSTERED,
Author VARCHAR(30),
Title VARCHAR(30),
Pages INT
)

The index key for this index is simply the column for which we defined the index, which in our case is the BookID column.

What if we want to create a compound index, which is an index that contains multiple columns as the key column? In that case, we need to define the index after the column list, like this:

CREATE TABLE Books
(
BookID INT INDEX idx_BookID CLUSTERED,
Author VARCHAR(30),
Title VARCHAR(30),
Pages INT,
INDEX idx_AuthorTitle(Author,Title)
)

(This particular index is nonclustered, FYI)

This is exactly what we do to create indexes in a table variable!

Folks, understand that this method of creating indexes on a table is exactly what we do to create indexes on table variables! We just need to define the indexes within the definition of the table variable.

Here’s an example where we basically convert our Books table to a table variable:

DECLARE @tempBooks AS TABLE
(
BookID INT INDEX idx_BookID CLUSTERED,
Author VARCHAR(30),
Title VARCHAR(30),
Pages INT,
INDEX idx_AuthorTitle(Author,Title)
)

If we add a few rows to this table variable, then run a simple query, we can see an example of the clustered index being used:

So simple!

Next Steps:

Leave a comment if you found this tutorial helpful!

Need to learn more about table variables in SQL Server? Check out the following beginner-friendly tutorial:

How to create a table variable in SQL Server

Also, if you want to learn more about indexes in SQL Server, check out this tutorial:

SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner

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, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!