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

(DE)
Mit dem Aufkommen der Columnstore Technologie und auch der In-Memory Technologie in SQL Server 2014 sind insgesamt 4 neue Indextypen zu den althergebrachten page-basierten hinzugekommen.

Wo es früher nur die Auswahl an „Clustered oder Non-Clustered“ (Deutsch auch „gruppiert oder nicht-gruppiert“) gab, gibt es mittlerweile auch Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexe.

Und seit SQL Server 2016 sind diese Indextypen untereinander noch weiter kombinierbar.
Vereinfacht ausgedrückt lassen sich nun sowohl die Page-basierten Indexe als auch die Memory-optimierten Tabellen mit Columnstore Indexen kombinieren.
Page(„Disk“)-basiert + Memory-optimiert funktioniert jedoch nicht.

(EN)
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.

Um auf schnelle Weise sehen zu können, welche Kombination an Indexen möglich ist, habe ich eine Matrix erstellt, welche die Kombinationsmöglichkeiten darstellt.
Angereichert mit Zusatzinformationen kann man den „Index-Spickzettel“ in Form einer pdf herunterladen. Und so sieht er dann aus:

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:

 Index Cheat-Sheet_preview

Download

Herunterladen kann man es hier bei Technet:

Download

You can download it here at Technet:

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

 

Insgesamt sind gibt es also mittlerweile 4 verschiedene Basis-Strukturen:

  1. Heaps
  2. Clustered Indexe page-basiert
  3. Clustered Columnstore Indexe und
  4. sogenannte varHeaps, die Struktur der memory-optimierten Tabellen.

In total, there are now 4 different basic structures:

  1. Heaps
  2. Clustered page-based indexes
  3. Clustered Columnstore indexes and
  4. so-called varHeaps, the structure of memory-optimized tables

- Nicht enthalten sind: Indexed Views, XML-Indexe und Räumliche (Spatial) Indexe, sowie die Implikationen bei Sparse-Columns.
Prinzipiell basieren auch diese alle noch auf den page-basierten b-Tree Indexen, sind durch die teilweise auf der CLR basierenden Datentypen jedoch wesentlich eingeschränkter.

- Unique Indexe habe ich nicht gesondert betrachtet, da sich diese für meine Betrachtung nicht anders als ihre Nicht-Unique Pedanten verhalten.

- 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 counterparts do.

Zusätzlich habe ich noch einige Grundregeln und Höchstgrenzen mit aufgenommen.

Dem aufmerksamen Leser wird dabei vielleicht auffallen, dass die maximale Anzahl der Index-Spalten in SQL Server 2016 von 16 auf 32 angehoben worden ist. – Bitte seht das aber nicht als Einladung an, diese Limit auch nur Ansatzweise auszunutzen!

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!

Ich hoffe diese Übersichten sind eine hilfreiche Erinnerungsstütze nicht  nur für Einsteiger, sondern auch für solche, die sich nicht immer an jede Regel erinnern können. Zum Weiterlesen sind auch einige Links auf BOL hinterlegt.

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 at BOL are also listed.

Hinweis: Die Übersicht basiert auf dem aktuellen Stand der Technik: SQL Server 2016.

Bereits gegenüber SQL Server 2014 gibt es wesentliche Unterschiede, und viel mehr noch zu 2012.
Kurz zusammengefasst gab es in der Version 2012 noch keine Memory-optimierten Tabellen und Clustered Columnstore Indexe. Diese kamen erst 2014 in den SQL Server. Und erst im 2016er gibt es die Kombinationsmöglichkeiten.

Folgende Artikel gab es zu diesen Themen bereits von mir:

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.

The following articles on these topics I have published before:

 

  1. The SQL Server 2016 In-Memory Evolution – from row-store to columnstore to in-memory operational analytics
  2. SQL Server 2016 – the Security & Performance Release / ein Sicherheits- und Performance-Release
  3. Artikel „SQL Server 2014 – Neues Fundament“ in iX Ausgabe 5/2014, Richtigstellungen zu In-Memory OLTP und ColumnStore Indexes und warum AlwaysOn nicht gleich Always On ist / SQL Server 2014 – New Fundament” in iX Issue 5/2014, corrections in In-Memory OLTP and ColumnStore Indexes, and why AlwaysOn is not the same as Always On
  4. The Columnstore Indexes & Batch Mode Processing (R)Evolution in SQL Server 2014

 

Happy Indexing

Andreas

  • adrian
    Comment from: adrian
    2016-08-01 @ 18:43:25

    Hi Andreas, first of all good job for putting together all the info about indexes, and this in a clear and concise form. What I’m missing from the cheat-sheet are the nonclustered indexes with included columns, which if I can remember correctly can support up to 1,023 columns in the INCLUDE clause. I consider them as important because remove the 900 bytes/32 columns key size restriction of indexes, plus the fact that they provide coverage for queries. In the first tabular representation one could consider also the partition as structure, this because it provides a different structure for tables as well for indexes. There are also some additional restrictions and further implications (see [1]). Apparently hash indexes and nonclustered columnstore indexes don’t have any restriction on index key size (see [2]). When working with indexes in particular, and with other SQL concepts in general, I think is important to know when there are as well when there are no limits altogether. Probably the "index limits" section can be represented in a table as well, as for each index one has to know the max number of indexes per table, the number of columns in the key and the maximum key size. Index limit 6: a limit of 16 columns applies for foreign keys as well. In SQL Server 2008 R2 spatial Indexes could be created only on a table that has a clustered primary key (see [3]). Was this restriction removed with coming versions? References: [1] MSDN (2016) CREATE INDEX https://msdn.microsoft.com/en-us/library/ms188783.aspx [2] SQL Server Database Engine Blog (2016) Increased nonclustered index key size with SQL Server 2016, by Jos de Bruijn https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/ [3] MSDN (2016) Restrictions on Spatial Indexes https://technet.microsoft.com/en-us/library/bb964740(v=sql.105).aspx

  • Comment from: Andreas Wolter
    2016-08-02 @ 11:08:56

    Hi Adrian Thank you for your helpful feedback and additions. Yes, included columns remove the limit of 32 columns in a nonclustered index. But “key columns” are still only 32 allowed. I will see if I can at least add a small note. Of course included columns are a very important feature. Similar goes for partitions. Actually the structure will be extended and be rather “multiple b-tree” indexes instead of a completely new structure. That was the reason (besides space) to leave them out. Probably it will make sense to have a second sheet “extension” with those let’s say advanced features. Spatial as well as XML indexes are left out on purpose. That would definitely be a call for a second sheet/page. Especially for spatial indexes there will be several terms that are not even slightly comparable to those listed here. Hence my comment “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.” If I see huge feedback asking for them I will consider including them. So far from my field experience they are a niche topic. (Although I personally love the spatial data functionalities personally very much). Yes, clustered primary keys are still a requirement for XML as well as spatial indexes. Again, thanks for your valuable feedback. I will integrate some of your notes for sure. It just might take a bit due to my workload. Andreas

  • Comment from: Andreas Wolter
    2016-08-02 @ 11:33:00

    PS: I just added a reference to "included columns" and found a small error in rule No10 which I corrected

  • green
    Comment from: green
    2016-09-08 @ 11:54:20

    i like this post

Leave a comment

Your email address will not be revealed on this site.

Your URL will be displayed.
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)
Bitte geben Sie den Namen dieser Domäne ohne http://www., aber mit Endung ein?
Please answer the question above.