https://www.insidesql.org/blogs/andreaswolter/?disp=comments en-EU http://backend.userland.com/rss 60 Thu, 08 Sep 2016 09:54:20 +0000 green [Visitor] c8351@https://www.insidesql.org/blogs/ i like this post https://www.insidesql.org/blogs/andreaswolter/2016/08/index-cheat-sheet-the-complete-index-compatibility-matrix-for-sqlserver#c8351 Tue, 02 Aug 2016 09:33:00 +0000 andreaswolter [Member] c8346@https://www.insidesql.org/blogs/ PS: I just added a reference to "included columns" and found a small error in rule No10 which I corrected https://www.insidesql.org/blogs/andreaswolter/2016/08/index-cheat-sheet-the-complete-index-compatibility-matrix-for-sqlserver#c8346 Tue, 02 Aug 2016 09:08:56 +0000 andreaswolter [Member] c8345@https://www.insidesql.org/blogs/ 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 https://www.insidesql.org/blogs/andreaswolter/2016/08/index-cheat-sheet-the-complete-index-compatibility-matrix-for-sqlserver#c8345 Mon, 01 Aug 2016 16:43:25 +0000 adrian [Member] c8344@https://www.insidesql.org/blogs/ 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 https://www.insidesql.org/blogs/andreaswolter/2016/08/index-cheat-sheet-the-complete-index-compatibility-matrix-for-sqlserver#c8344