Non-Clustered Indexes on Table Variables in SQL Server 2014

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Earlier today I have seen a Tweet from Paul White, where he tweeted that SQL Server 2014 supports Non-Unique Clustered and Non-Clustered Indexes for Table Variables in SQL Server 2014. So I had to start up my virtual machine and try it out, because this would be a awesome new feature. Table Variables are great, because you can avoid excessive recompilations with them. They have no statistics, and when you are creating them, you are not changing the database schema. They are just variables, but still persisted in TempDb.

One drawback is that you where not able to create Non-Clustered Indexes on them, which is bad when you are dealing with a larger dataset. But with SQL Server 2014 CTP1 that behavior is now changed. Just have a look on the following code:

When you look on the Execution Plan of the SELECT Statement, SQL Server is executing a Non-Clustered Index Seek operator. As you can see you are now able to create additional Non-Clustered indexes on the Table Variable. Each created Non-Clustered Index has no Statistics Object attached to it. It’s a very nice, easy syntax which is also supported on “normal” database tables that you create. Let’s have a look on the following table definition:

Furthermore it’s also possible to create composite indexes with the new syntax:

Very nice :-)

Thanks for reading

-Klaus

Do you like this post? Why not share it with the SQL Server community?