Maximum Capacity Details Related to Indexes in SQL Server

Posted on : 16-04-2012 | By : Devi Prasad | In : SQL Server Basics, SQL Server Management

Share:

0


Below are the Maximum capacity details related to Indexes Supported By SQL Server:

Max Capacity For SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 SQL Server 2005
Maximum number of Clustered Indexes on a table 1 Clustered Index 1 Clustered Index 1 Clustered Index 1 Clustered Index
Maximum number of Non Clustered Indexes on a table 999 Non Clustered Indexes 999 Non Clustered Indexes 999 Non Clustered Indexes 249 Non Clustered Indexes
Maximum number of Bytes per Index Key 900 Bytes 900 Bytes 900 Bytes 900 Bytes
Maximum number of Columns per index key 16 Columns 16 Columns 16 Columns 16 Columns
Maximum number of Partitions per partitioned table or index 15,000 Partitions 1,000 Partitions 1,000 Partitions 1,000 Partitions
Maximum number of Statistics on non-indexed columns 30,000 Statistics 30,000 Statistics 30,000 Statistics 2,000 Statistics
Maximum number of XML indexes 249 XML indexes 249 XML indexes 249 XML indexes 249 XML indexes




In Detail:
SQL Server supports:

  • 1 clustered index per table.
  • 999 Non Clustered Indexes per table for SQL Server 2012, SQL Server 2008 R2, SQL Server 2008
  • 249 Non Clustered Indexes per table for SQL Server 2005
  • 900 Bytes per Index Key
  • 16 Columns per index key
  • 15,000 Partitions per partitioned table or index in SQL Server 2012
  • 1,000 Partitions per partitioned table or index in SQL Server 2008 R2, SQL Server 2008,SQL Server 2005
  • 30,000 Statistics on non-indexed columns
  • 249 XML indexes

References:
http://msdn.microsoft.com/en-us/library/ms143432.aspx
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.90).aspx




References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,