Are Table Variable Indexes Ever Useful?

Oughtta Know

Indexes are good for so much more than what they’re given credit for by the general public.

One example where indexes can be useful is with the oft-maligned table variable.

Now, they won’t help you get a better estimate from a table variable. In versions prior to the upcoming 2019 release, table variables will only net you a single row estimate.

Yes, you can recompile to get around that. Yes, you can use a trace flag to occasionally be helpful with that.

Those defenses are inadequate, and you know it.

Help How?

Let’s say we have this query against a table variable.

With an unindexed table variable, the plan looks like this:

Sucko

You can see by the helpful new operator time stats in SSMS 18 that this query runs for 13.443 seconds.

Of that, 13.333 seconds is spent scanning the table variable. Bad guess? You bet.

If we change the table variable definition to include an index, the plan changes, and runs much faster.

Holla holla

The query no longer goes parallel, but it runs for 226ms.

A significant change aside from parallelism is that the Top operator is no longer a Top N Sort.

The clustered index has put the table variable data in useful order for our query.

Insertions

The table variable insert looks like this:

Right now, I’ve got the index definition quoted out. The insert runs for .662ms.

Oh Boy

The insert with the index in place runs for .967ms:

Grab an umbrella

Given the 13 second improvement to the final query, I’ll take the ~300ms hit on this one.

Wierda

If you’re wondering why I’ve got the insert query broken up with a UNION ALL, it’s because the alternative really sucks:

@_@

This insert takes 1.4 seconds, and introduces a spilling sort operator.

So uh, don’t do that IRL.

Thanks for reading!

2 thoughts on “Are Table Variable Indexes Ever Useful?”

  1. Leaving performance reasons aside for the moment, we use indexes on table variables kind of.
    Our way of sending (small) sets of ints or bigints as an argument to sprocs uses table variables.
    As a development decision, some of these sets use primary keys (then the procedure can count on uniqueness).
    That’s one kind of index on a TVP

Leave a Reply

Your email address will not be published. Required fields are marked *