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

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!

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.

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

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

Remember me(Set cookies so I don't need to fill out my details next time) Allow message form(Allow users to contact me through a message form -- Your email will not be revealed!) Notify me of replies

*Antispam:

Bitte geben Sie den Namen dieser Domäne ohne http://www., aber mit Endung ein?Please answer the question above.We ask for this in order to slow down spammers.Sorry for the inconvenience.Please log in to avoid this antispam check.