{"id":132,"date":"2012-04-16T10:22:31","date_gmt":"2012-04-16T10:22:31","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/04\/16\/maximum-capacity-details-related-to-indexes-in-sql-server\/"},"modified":"2012-04-16T10:22:31","modified_gmt":"2012-04-16T10:22:31","slug":"maximum-capacity-details-related-to-indexes-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/04\/16\/maximum-capacity-details-related-to-indexes-in-sql-server\/","title":{"rendered":"Maximum Capacity Details Related to Indexes in SQL Server"},"content":{"rendered":"<p>table.hovertable {<br \/>\nfont-family: verdana,arial,sans-serif;<br \/>\nfont-size:11px;<br \/>\ncolor:#333333;<br \/>\nborder-width: 1px;<br \/>\nborder-color: #999999;<br \/>\nborder-collapse: collapse;<br \/>\n}<br \/>\ntable.hovertable th {<br \/>\nbackground-color:#c3dde0;<br \/>\nborder-width: 1px;<br \/>\npadding: 8px;<br \/>\nborder-style: solid;<br \/>\nborder-color: #a9c6c9;<br \/>\n}<br \/>\ntable.hovertable tr {<br \/>\nbackground-color:#d4e3e5;<br \/>\n}<br \/>\ntable.hovertable td {<br \/>\nborder-width: 1px;<br \/>\npadding: 8px;<br \/>\nborder-style: solid;<br \/>\nborder-color: #a9c6c9;<br \/>\n}<\/p>\n<p><strong>Below are the Maximum capacity details related to Indexes Supported By SQL Server:<\/strong><\/p>\n<table class=\"hovertable\">\n<tbody>\n<tr align=\"center\">\n<th>Max Capacity For<\/th>\n<th>SQL Server 2012<\/th>\n<th>SQL Server 2008 R2<\/th>\n<th>SQL Server 2008<\/th>\n<th>SQL Server 2005<\/th>\n<\/tr>\n<tr>\n<td>\n<strong>Maximum number of Clustered Indexes on a table <\/strong>\n<\/td>\n<td>\n1 Clustered Index\n<\/td>\n<td>\n1 Clustered Index\n<\/td>\n<td>\n1 Clustered Index\n<\/td>\n<td>\n1 Clustered Index\n<\/td>\n<\/tr>\n<\/tr>\n<td>\n<strong>Maximum number of Non Clustered Indexes  on a table<\/strong>\n<\/td>\n<td>\n999 Non Clustered Indexes\n<\/td>\n<td>\n999 Non Clustered Indexes\n<\/td>\n<td>\n999 Non Clustered Indexes\n<\/td>\n<td>\n249 Non Clustered Indexes\n<\/td>\n<\/tr>\n<\/tr>\n<td>\n<strong>Maximum number of Bytes per Index Key<\/strong>\n<\/td>\n<td>\n900 Bytes\n<\/td>\n<td>\n900 Bytes\n<\/td>\n<td>\n900 Bytes\n<\/td>\n<td>\n900 Bytes\n<\/td>\n<\/tr>\n<\/tr>\n<td>\n<strong>Maximum number of Columns per index key<\/strong>\n<\/td>\n<td>\n16 Columns\n<\/td>\n<td>\n16 Columns\n<\/td>\n<td>\n16 Columns\n<\/td>\n<td>\n16 Columns\n<\/td>\n<\/tr>\n<\/tr>\n<td>\n<strong>Maximum number of Partitions per partitioned table or  index<\/strong>\n<\/td>\n<td>\n15,000 Partitions\n<\/td>\n<td>\n1,000 Partitions\n<\/td>\n<td>\n1,000 Partitions\n<\/td>\n<td>\n1,000 Partitions\n<\/td>\n<\/tr>\n<\/tr>\n<td>\n<strong>Maximum number of Statistics on non-indexed columns<\/strong>\n<\/td>\n<td>\n30,000 Statistics\n<\/td>\n<td>\n30,000 Statistics\n<\/td>\n<td>\n30,000 Statistics\n<\/td>\n<td>\n2,000 Statistics\n<\/td>\n<\/tr>\n<\/tr>\n<td>\n<strong>Maximum number of XML indexes<\/strong>\n<\/td>\n<td>\n249 XML indexes\n<\/td>\n<td>\n249 XML indexes\n<\/td>\n<td>\n249 XML indexes\n<\/td>\n<td>\n249 XML indexes\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><em>In Detail:<\/em><br \/>\n<strong>SQL Server supports:<\/strong> <\/p>\n<ul>\n<li>1 clustered index per table.<\/li>\n<li>999 Non Clustered Indexes per table for SQL Server 2012, SQL Server 2008 R2, SQL Server 2008 <\/li>\n<li>249 Non Clustered Indexes per table for SQL Server 2005<\/li>\n<li>900 Bytes per Index Key<\/li>\n<li>16 Columns per index key<\/li>\n<li>15,000 Partitions per partitioned table or  index in SQL Server 2012<\/li>\n<li>1,000 Partitions per partitioned table or  index in SQL Server 2008 R2, SQL Server 2008,SQL Server 2005<\/li>\n<li>30,000 Statistics on non-indexed columns<\/li>\n<li>249 XML indexes<\/li>\n<\/ul>\n<p>References:<br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432.aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432.aspx<\/a><br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432(v=sql.105).aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432(v=sql.105).aspx<\/a><br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432(v=sql.100).aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432(v=sql.100).aspx<\/a><br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432(v=sql.90).aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/ms143432(v=sql.90).aspx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>table.hovertable { font-family: verdana,arial,sans-serif; font-size:11px; color:#333333; border-width: 1px; border-color: #999999; border-collapse: collapse; } table.hovertable th { background-color:#c3dde0; border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } table.hovertable tr { background-color:#d4e3e5; } table.hovertable td { border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } Below are the Maximum capacity details related to Indexes Supported By SQL&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,273],"tags":[1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/132"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=132"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/132\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}