What Kind Of Indexes Can You Create On SQL Server Temporary Objects?

Posted on by Erik Darling

That, There

Intermediate result materialization is one of the most successful tuning methods that I regularly use.

Separating complexity is only second to eliminating complexity. Of course, sometimes those intermediate results need indexing to finish the job.

Not always. But you know. I deal with some weird stuff.

Like anime weird.

Listings!

Regular ol’ #temp tables can have just about any kind of index plopped on them. Clustered, nonclustered, filtered, column store (unless you’re using in-memory tempdb with SQL Server 2019). That’s nice parity with regular tables.

Of course, lots of indexes on temp tables have the same problem as lots of indexes on regular tables. They can slow down loading data in, especially if there’s a lot. That’s why I usually tell people load first, create indexes later.

There are a couple ways to create indexes on #temp tables:

Create, then add

/*Create, then add*/
CREATE TABLE #t (id INT NOT NULL);
/*insert data*/
CREATE CLUSTERED INDEX c ON #t(id);

Create inline

/*Create inline*/
CREATE TABLE #t(id INT NOT NULL,
                INDEX c CLUSTERED (id));

It depends on what problem you’re trying to solve:

  • Recompiles caused by #temp tables: Create Inline
  • Slow data loads: Create, then add

Another option to help with #temp table recompiles is the KEEPFIXED PLAN hint, but to wit I’ve only ever seen it used in sp_WhoIsActive.

Forgotten

Often forgotten is that table variables can be indexed in many of the same ways (at least post SQL Server 2014, when the inline index create syntax came about). The only kinds of indexes that I care about that you can’t create on a table variable are column store and filtered (column store generally, filtered pre-2019).

Other than that, it’s all fair game.

DECLARE @t TABLE( id INT NOT NULL,
                  INDEX c CLUSTERED (id),
				  INDEX n NONCLUSTERED (id) );

You can create clustered and nonclustered indexes on them, they can be unique, you can add primary keys.

It’s a whole thing.

Futuristic

In SQL Server 2019, we can also create indexes with included columns and filtered indexes with the inline syntax.

CREATE TABLE #t( id INT, 
                 more_id INT, 
				 INDEX c CLUSTERED (id),
                 INDEX n NONCLUSTERED (more_id) INCLUDE(id),
				 INDEX f NONCLUSTERED (more_id) WHERE more_id > 1 );


DECLARE @t TABLE ( id INT, 
                   more_id INT, 
				   INDEX c CLUSTERED (id),
                   INDEX n NONCLUSTERED (more_id) INCLUDE(id),
				   INDEX F NONCLUSTERED (more_id) WHERE more_id > 1 );

 

Missing Persons

Notice that I’m not talking about CTEs here. You can’t index create indexes on those.

Perhaps that’s why they’re called “common”.

Yes, you can index the underlying tables in your query, but the results of CTEs don’t get physically stored anywhere that would allow you to create an index on them.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts