Index-Cheat-Sheet. The complete Index-compatibility Matrix for SQL Server

With the total of 4 new index types we have gained since SQL Server 2012 now we also have a total of 4 basic formats (Heap, Clustered Index, Clustered Columnstore Index, memory-optimized “varheap”) for data storage in SQL Server. And what’s more, with SQL Server 2016, these can be combined in various ways. In this article, Andreas Wolter makes available two neatly arranged “compatibility matrixes” that allow for quick combination possibilities. Added bonus: you can download it as single-page PDF document and if you like pin it to your wall. 🙂

With the emergence of the Columnstore technology and the In-Memory technology in SQL Server 2014, a total of 4 new index types have been added to the conventional page-based types.

When previously there had only been the choice of “Clustered or Non-Clustered,” now there are also Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexes.

And since SQL Server 2016, these index types can be further combined. Simply put, now it is possible to combine both page-based indexes and memory-optimized tables with Columnstore indexes.

However, Page (“Disk”)-based + Memory-optimized does not work.

In order to quickly determine which combination in indexes is possible, I created a matrix that illustrates the combination options.

Completed with additional information, you can download the “index cheat sheet” as a PDF document. This is how it looks:

Download

You can download it here at TechNet:

gallery.technet.microsoft.com/Index-Cheat-Sheet-The-8378ac1b

In total, there are now 4 different basic structures:

Heaps
Clustered page-based indexes
Clustered Columnstore indexes and
so-called varHeaps, the structure of memory-optimized tables

– Not included are: Indexed Views, XML-Indexes and spatial indexes, as well as the implications in Sparse-Columns.

Generally, the latter are all still based on the page-based b-Tree indexes, but are significantly more restricted due to the data types partially based on CLR.

I did not consider Unique Indexes separately as for the purpose of my consideration they do not behave differently to the way their non-unique couterparts do.

In addition, I have included a few basic rules and maximum limits.

The attentive reader may notice that the maximum number of index columns in SQL Server 2016 has increased from 16 to 32. – But please do not regard it as an invitation to even attempt to exploit these limits!

I hope these overviews are a helpful mnemonic device not only for beginners but also for those who don’t always remember every single rule. Links for further readings are listed at BOL.

Note: This overview is based on the current technical state: SQL Server 2016.

There are already significant differences to SQL Server 2014, and even more to 2012.

In a nutshell, in the 2012 version, there had not been any memory-optimized tables or Clustered Columnstore indexes. They only made it into the SQL Server in 2014. And only with the 2016 SQL Server there are the combination possibilities.

Before my article, there have already been the following articles on these topics:

Happy Indexing

Andreas