What is the maximum number of Index per table?
For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index
http://msdn.microsoft.com/en-us/library/ms143432.aspx
The following table specifies the maximum sizes and numbers of various objects defined in SQL Server 2005 databases or referenced in Transact-SQL statements. The table does not include SQL Server Windows CE Edition.
Thanks
Alok Kumar sharm
For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index
http://msdn.microsoft.com/en-us/library/ms143432.aspx
The following table specifies the maximum sizes and numbers of various objects defined in SQL Server 2005 databases or referenced in Transact-SQL statements. The table does not include SQL Server Windows CE Edition.
| SQL Server 2005 Database Engine object | Maximum sizes/numbers SQL Server 2005 (32-bit) | Maximum sizes/numbers SQL Server 2005 (64-bit) |
|---|---|---|
|
Batch size1 |
65,536 * Network Packet Size |
65,536 * Network Packet Size |
|
Bytes per short string column |
8,000 |
8,000 |
|
Bytes per GROUP BY, ORDER BY |
8,060 |
8,060 |
|
Bytes per index key2 |
900 |
900 |
|
Bytes per foreign key |
900 |
900 |
|
Bytes per primary key |
900 |
900 |
|
Bytes per row8 |
8,060 |
8,060 |
|
Bytes per varchar(max), varbinary(max), xml, text, or image column |
2^31-1 |
2^31-1 |
|
Characters per ntext or nvarchar(max) column |
2^30-1 |
2^30-1 |
|
Clustered indexes per table |
1 |
1 |
|
Columns in GROUP BY, ORDER BY |
Limited only by number of bytes |
Limited only by number of bytes |
|
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement |
10 |
10 |
|
Columns per index key7 |
16 |
16 |
|
Columns per foreign key |
16 |
16 |
|
Columns per primary key |
16 |
16 |
|
Columns per base table |
1,024 |
1,024 |
|
Columns per SELECT statement |
4,096 |
4,096 |
|
Columns per INSERT statement |
1,024 |
1,024 |
|
Connections per client |
Maximum value of configured connections |
Maximum value of configured connections |
|
Database size |
524,258 terabytes |
524,258 terabytes |
|
Databases per instance of SQL Server |
32,767 |
32,767 |
|
Filegroups per database |
32,767 |
32,767 |
|
Files per database |
32,767 |
32,767 |
|
File size (data) |
16 terabytes |
16 terabytes |
|
File size (log) |
2 terabytes |
2 terabytes |
|
Foreign key table references per table4 |
253 |
253 |
|
Identifier length (in characters) |
128 |
128 |
|
Instances per computer |
50 instances on a stand-alone server for all SQL Server
2005 editions except for Workgroup Edition. Workgroup Edition supports a
maximum of 16 instances. SQL Server 2005 supports 25 instances on a failover cluster. |
50 instances on a stand-alone server. 25 instances on a failover cluster. |
|
Length of a string containing SQL statements (batch size)1 |
65,536 * Network packet size |
65,536 * Network packet size |
|
Locks per connection |
Maximum locks per server |
Maximum locks per server |
|
Locks per instance of SQL Server5 |
Up to 2,147,483,647 |
Limited only by memory |
|
Nested stored procedure levels6 |
32 |
32 |
|
Nested subqueries |
32 |
32 |
|
Nested trigger levels |
32 |
32 |
|
Nonclustered indexes per table |
249 |
249 |
|
Parameters per stored procedure |
2,100 |
2,100 |
|
Parameters per user-defined function |
2,100 |
2,100 |
|
REFERENCES per table |
253 |
253 |
|
Rows per table |
Limited by available storage |
Limited by available storage |
|
Tables per database3 |
Limited by number of objects in a database |
Limited by number of objects in a database |
|
Partitions per partitioned table or index |
1,000 |
1,000 |
|
Statistics on non-indexed columns |
2,000 |
2,000 |
|
Tables per SELECT statement |
256 |
256 |
|
Triggers per table3 |
Limited by number of objects in a database |
Limited by number of objects in a database |
|
UNIQUE indexes or constraints per table |
249 nonclustered and 1 clustered |
249 nonclustered and 1 clustered |
|
User connections |
32,767 |
32,767 |
|
XML indexes |
249 |
249 |
Thanks
Alok Kumar sharm