{"id":2332,"date":"2016-08-01T16:45:33","date_gmt":"2016-08-01T15:45:33","guid":{"rendered":"http:\/\/andreas-wolter.com\/index-cheat-sheet-die-vollstaendige-index-kompatibilitaets-matrix-fuer-sql-server\/"},"modified":"2017-10-18T10:38:59","modified_gmt":"2017-10-18T09:38:59","slug":"index-cheat-sheet","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/index-cheat-sheet\/","title":{"rendered":"Index-Cheat-Sheet. The complete Index-compatibility Matrix for SQL Server"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-26a8ce315fba8228bed61cd59d7423a6\">\n#top .av-special-heading.av-av_heading-26a8ce315fba8228bed61cd59d7423a6{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-26a8ce315fba8228bed61cd59d7423a6 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-26a8ce315fba8228bed61cd59d7423a6 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-26a8ce315fba8228bed61cd59d7423a6 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >Index-Cheat-Sheet. The complete Index-compatibility Matrix for SQL Server<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>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 \u201cvarheap\u201d) for data storage in SQL Server. And what\u2019s more, with SQL Server 2016, these can be combined in various ways. <!--more-->In this article, Andreas Wolter makes available two neatly arranged \u201ccompatibility matrixes\u201d 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. \ud83d\ude42<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>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.<\/p>\n<p>When previously there had only been the choice of \u201cClustered or Non-Clustered,\u201d now there are also <strong>Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexes.<\/strong><\/p>\n<p>And since SQL Server 2016, these index types can be further combined. Simply put, now it is possible to <strong>combine both page-based indexes and memory-optimized tables with Columnstore indexes<\/strong>.<\/p>\n<p><strong>However, Page (\u201cDisk\u201d)-based + Memory-optimized does not work. <\/strong><\/p>\n<p>In order to quickly determine which combination in indexes is possible, I created a <strong>matrix that illustrates the combination options<\/strong>.<\/p>\n<p>Completed with additional information, you can download the \u201cindex cheat sheet\u201d as a PDF document. This is how it looks:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-8f4a221bd70504e7067655a7b8dd2cf7\">\n.avia-image-container.av-av_image-8f4a221bd70504e7067655a7b8dd2cf7 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-8f4a221bd70504e7067655a7b8dd2cf7 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-8f4a221bd70504e7067655a7b8dd2cf7 av-styling- avia-align-center  avia-builder-el-3  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2327 avia-img-lazy-loading-not-2327 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet.png\" alt='' title='Index_Cheat_Sheet'  height=\"510\" width=\"720\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet.png 720w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet-600x425.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet-300x213.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet-260x185.png 260w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet-705x499.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/Index_Cheat_Sheet-450x319.png 450w\" sizes=\"(max-width: 720px) 100vw, 720px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Download<\/strong><\/p>\n<p>You can download it here at TechNet:<\/p>\n<p><a href=\"https:\/\/gallery.technet.microsoft.com\/Index-Cheat-Sheet-The-8378ac1b\" target=\"_blank\" rel=\"noopener\">gallery.technet.microsoft.com\/Index-Cheat-Sheet-The-8378ac1b<\/a><\/p>\n<p>In total, there are now <strong>4 <\/strong>different basic structures:<\/p>\n<ol>\n<li><b><strong>Heaps<\/strong><\/b><\/li>\n<li><strong>Clustered page-based indexes<\/strong><\/li>\n<li><strong>Clustered Columnstore indexes<\/strong> and<\/li>\n<li>so-called <strong>varHeaps<\/strong>, the structure of <strong>memory-optimized tables<\/strong><\/li>\n<\/ol>\n<p>&#8211; <strong>Not included <\/strong>are: Indexed Views, XML-Indexes and spatial indexes, as well as the implications in Sparse-Columns.<\/p>\n<p>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.<\/p>\n<ul>\n<li>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.<\/li>\n<\/ul>\n<p><strong>In addition<\/strong>, I have included a few <strong>basic rules<\/strong> and <strong>maximum limits<\/strong>.<\/p>\n<p>The attentive reader may notice that the maximum number of index columns in SQL Server 2016 has increased from 16 to 32. \u2013 But please do not regard it as an invitation to even attempt to exploit these limits!<\/p>\n<p>I hope these overviews are a helpful mnemonic device not only for beginners but also for those who don\u2019t always remember every single rule. Links for further readings are listed at BOL.<\/p>\n<p>Note: <strong>This overview is based on the current technical state: SQL Server 2016<\/strong>.<\/p>\n<p>There are already significant differences to SQL Server 2014, and even more to 2012.<\/p>\n<p>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.<\/p>\n<p>Before my article, there have already been the following articles on these topics:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><ol>\n<li><a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2016\/07\/the-sql-server-2016-in-memory-evolution-from-row-store-to-columnstore-to-in-memory-operational-analytics\" target=\"_blank\" rel=\"noopener\">The SQL Server 2016 In-Memory Evolution \u2013 from row-store to columnstore to in-memory operational analytics<\/a><\/li>\n<li><a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2015\/05\/sql-server-2016-the-security-and-performance-release\" target=\"_blank\" rel=\"noopener\">SQL Server 2016 \u2013 the Security &#038; Performance Release \/ ein Sicherheits- und Performance-Release<\/a><\/li>\n<li><a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2014\/05\/artikel-sql-server-2014-richtigstellungen\" target=\"_blank\" rel=\"noopener\">Artikel \u201eSQL Server 2014 \u2013 Neues Fundament\u201c in iX Ausgabe 5\/2014, Richtigstellungen zu In-Memory OLTP und ColumnStore Indexes und warum AlwaysOn nicht gleich Always On ist \/ SQL Server 2014 \u2013 New Fundament\u201d in iX Issue 5\/2014, corrections in In-Memory OLTP and ColumnStore Indexes, and why AlwaysOn is not the same as Always On<\/a><\/li>\n<li><a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2013\/06\/sql-server-2014-columnstore-indexes-batch-mode-improvements\" target=\"_blank\" rel=\"noopener\">The Columnstore Indexes &#038; Batch Mode Processing (R)Evolution in SQL Server 2014<\/a><\/li>\n<\/ol>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Happy Indexing<\/p>\n<p>Andreas<\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-7  el_after_av_textblock  el_before_av_one_full '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-8  el_after_av_hr  el_before_av_social_share  first flex_column_div av-zero-column-padding  '     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-10  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/index-cheat-sheet\/&#038;t=Index-Cheat-Sheet.%20The%20complete%20Index-compatibility%20Matrix%20for%20SQL%20Server\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=Index-Cheat-Sheet.%20The%20complete%20Index-compatibility%20Matrix%20for%20SQL%20Server&#038;url=https:\/\/andreas-wolter.com\/en\/?p=2332\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=Index-Cheat-Sheet.%20The%20complete%20Index-compatibility%20Matrix%20for%20SQL%20Server&#038;url=https:\/\/andreas-wolter.com\/en\/index-cheat-sheet\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-11  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"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 \u201cvarheap\u201d) for data storage in SQL Server. And what\u2019s more, with SQL Server 2016, these can be combined in various ways.","protected":false},"author":4,"featured_media":2329,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73,46,118,121,45,44],"tags":[],"class_list":["post-2332","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-columnstore-index","category-in-memory-oltpxtp-htap","category-indexing-en","category-scripts-en","category-sql-server-2016","category-storage-engine"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2332","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=2332"}],"version-history":[{"count":6,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2332\/revisions"}],"predecessor-version":[{"id":4913,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2332\/revisions\/4913"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/2329"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=2332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=2332"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=2332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}