{"id":577,"date":"2008-04-16T09:44:00","date_gmt":"2008-04-16T09:44:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Indexes-in-SQL-Server-20052008-Best-Practices-Part-1.aspx"},"modified":"2013-01-11T23:12:05","modified_gmt":"2013-01-12T07:12:05","slug":"indexes-in-sql-server-20052008-best-practices-part-1","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/indexes-in-sql-server-20052008-best-practices-part-1\/","title":{"rendered":"Indexes in SQL Server 2005\/2008 &#8211; Best Practices, Part 1"},"content":{"rendered":"<p>\nIn my blog post on my new sp_helpindex proc (<a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/2008\/04\/02\/sphelpindex2toshowincludedcolumns2005andfilteredindexes2008whicharenotshownbysphelpindex.aspx\">sp_helpindex2<\/a>), I mentioned that the indexes in my sample were not necessarily a recommended set of indexes &#8211; just a test set of indexes. So&#8230; in this post, I thought I&#39;d start a series on indexes, limitations and best practices\/uses&#8230; Especially, why\/how to best choose when to use INCLUDE v. having columns in your key. To start, I thought I&#39;d give some background, as well as limitations that exist in various releases from 2005 to 2008 CTP6 (Feb CTP), plus what&#39;s <em>expected <\/em>in the SQL Server 2008 RTM (<em>ah&#8230; I did say &quot;expected&quot; so don&#39;t come back and yell at me if\/when I&#39;m wrong <\/em>;-))\n<\/p>\n<p>\nFirst, let&#39;s go through a few rules and limitations and background:\n<\/p>\n<p>\n<strong>SQL Server 2005<\/strong>\n<\/p>\n<ul>\n<li>250 total indexes per table: 1 clustered index and up to 249 nonclustered indexes (no, this is not a goal!) <\/li>\n<li>The index key can be up to 16 columns OR 900 bytes &#8211; whichever comes first&#8230; <\/li>\n<li>The leaf level is limited just as a table is limited to 1024 columns (and, all column types are acceptable in the leaf level of an index &#8211; even LOB columns) <\/li>\n<li>Statistics are kept for every index (so, up to 250 index-related stats) and there can also be statistics on columns or sets of columns&nbsp;in addition to the index-related stats. In earlier releases, statistics used index ids and as a result, the number of statistics were limited to 250 total minus the statistics used by indexes&#8230; in SQL Server 2005, they changed to having statistics kept\/managed separately (use sys.stats to see them). As a result of using sys.stats, you can now have 2000 statistics on a table, in addition to the 250 (total) indexes and their statistics. If you want to test this out (and check it on various versions of SQL Server), use this script to setup a test database, a test table and then use dynamic string execution to loop through (until it errors) with creating nonclustered indexes and statistics: <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/4\/indexmax.sql\">IndexMax.sql (853.00 bytes)<\/a>.<\/li>\n<\/ul>\n<p>\n<strong>SQL Server 2008 CTP6<\/strong>\n<\/p>\n<ul>\n<li>So far, it seems as though most of the maximums have not yet been lifted&#8230; <\/li>\n<li>250 total indexes per table: 1 clustered index and up to 249 nonclustered indexes (and this number&nbsp; &#8211; for CTP6 &#8211; includes filtered indexes AND spatial indexes too!) <\/li>\n<li>The index key limit hasn&#39;t changed (it can be up to 16 columns OR 900 bytes &#8211; whichever comes first) <\/li>\n<li>The leaf level is still limited just as a table is limited to 1024 columns (and, all column types are still acceptable in the leaf level of an index) <\/li>\n<li>Statistics in CTP6 seem to be limited to only 2000 stats per table&#8230;<\/li>\n<\/ul>\n<p>\n<strong>SQL Server 2008 RTM <\/strong>(<em>expected\/target&#8230; no guarantees on this one<\/em> :)\n<\/p>\n<ul>\n<li>30,000 columns per table (mostly to allow sparse columns) <\/li>\n<li>1,000 total indexes per table: 1 clustered index and up to&nbsp;999 nonclustered indexes. This is also not a goal BUT, it makes sense because of both sparse columns and filtered indexes. Both Paul and I will try to post some entries about sparse columns and filtered indexes in the coming days&#8230; <\/li>\n<li>The index key limit won&#39;t&nbsp;change <\/li>\n<li>The leaf level is will be limited just as a table is limited to&nbsp;30,000 columns (and, all column types are still acceptable in the leaf level of an index) <\/li>\n<li>Statistics are also said to be increasing and likely to 30,000&#8230; And, for having extra statistics just sitting around and possibly not being used &#8211; well, outside of a minimal amount of disk space taken by the stat blob (which does start to get interesting at 1,000s I suppose), even stats that don&#39;t get used don&#39;t really create much of a problem. So, I&#39;m OK with this one increasing &#8211; even significantly &#8211; but I have to admit I&#39;m somewhat nervous about the significan&#39;t increase in indexes&#8230;&#8230;&#8230;<\/li>\n<\/ul>\n<p>\nSo&#8230; you can have A LOT more indexes in SQL Server 2008 but just because you can &#8211; DOES it mean that you should?!\n<\/p>\n<p>\nAnd on that &#8211; I&#39;ll leave you hanging for my next post where I start to talk about WHY they&#39;re increasing this (hint: sparse columns and filtered indexes = more columns\/more indexes)&#8230;.\n<\/p>\n<p>\nHave fun,<br \/>\nkt\n<\/p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my blog post on my new sp_helpindex proc (sp_helpindex2), I mentioned that the indexes in my sample were not necessarily a recommended set of indexes &#8211; just a test set of indexes. So&#8230; in this post, I thought I&#39;d start a series on indexes, limitations and best practices\/uses&#8230; Especially, why\/how to best choose when [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,65,66],"tags":[],"class_list":["post-577","post","type-post","status-publish","format-standard","hentry","category-indexes","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=577"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/577\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}