Creating an index on a table variable

sql

indexing-strategies

sql-server

performance-optimization

byAlex Kataev·Jan 1, 2025

You can create an index on a table variable using the DECLARE statement. This functionality is available in SQL Server 2014+. Here's a straightforward example:

DECLARE @TempTable TABLE (
    ID INT,
    Data VARCHAR(100),
    INDEX IDX_Data NONCLUSTERED (Data)
);

In the example above, a nonclustered index IDX_Data is defined on the Data column for the @TempTable table variable - useful for optimizing queries that filter or sort by Data.

Indexing in SQL Server 2000 to 2012: unique and primary key constraints

In SQL Server 2000-2012, you can't explicitly create non-unique indexes, but you can apply primary key or unique constraints which implicitly create indexes:

DECLARE @TempTable TABLE (
    ID INT PRIMARY KEY, -- Swaggered Clustered Index 
    Data VARCHAR(100) UNIQUE -- Low-key Non-Clustered Index 
);

Here, PRIMARY KEY automatically creates a clustered index and UNIQUE creates a non-clustered index. If you want a non-unique clustered index, add an IDENTITY column as a uniqueifier.

Got a non-unique clustered index? Here's a hack

Creating a non-unique clustered index in old SQL Server versions needs a bit of jigging. You can use an IDENTITY column to act as a faux unique key:

DECLARE @TempTable TABLE (
    ID INT IDENTITY(1,1),
    Data VARCHAR(100),
    UNIQUE CLUSTERED(ID, Data) -- Clustered Index playing hide & seek
);

For non-unique non-clustered indexes, SQL Server gives them a makeover by adding a row locator ensuring their uniqueness.

The case for temporary tables

Table variables can fall short due to the lack of statistics, which potentially negates query optimization. For heavy-duty tasks, opt for temporary tables (#temp) that support full index creation and come with the benefits of statistics.

CREATE TABLE #Temp (
    ID INT PRIMARY KEY,
    Data VARCHAR(100)
);

CREATE NONCLUSTERED INDEX IDX_Data ON #Temp (Data); -- Indexing the X factor

Unique constraints and the NULL affair

Dealing with unique constraints can get tricky due to SQL Server's take on NULL values. SQL Server views each NULL as unique, deviating from the SQL Standard.

SQL Server 2014+: inline non-unique indexes

From SQL Server 2014 onwards, non-unique indexes can be created inline. Yet, conventional wisdom still favors temporary tables for major data manipulations.

Advanced tips for professional developers

Dynamic SQL: bypassing index naming issues

Use dynamic SQL to circumvent any potential name clashes in tempdb:

EXEC sp_executesql N'CREATE INDEX IDX_Dynamic ON #TempDynamic (DataColumn)'; -- Index in disguise

Filtered indexes

In SQL Server 2016+, it's possible to create filtered indexes for specific datasets:

CREATE INDEX IDX_FilteredData ON #Temp (Data) WHERE Data IS NOT NULL; -- Filtered Index got your back!

These are excellent for performance optimization when used adroitly.

When should you consider table variables?

Consider table variables when:

The dataset is small and won't adapt well to indexing beyond primary/unique constraints.
You need quick and disposable storage without tempdb
The scope is constricted to a single batch or procedure, avoiding the lifespan complexities temporary tables may bring.

Using indexing strategies right, whether in table variables or temporary tables, can power your application from sluggish to snappy in no time!