Limit on indexes per table in SQL Server.

[borach]

Are there any limitations for the number of indexes per table in SQL Server 7.0 (or per database for that matter). I know there are in Access 2000 (namely 32 indexes per table). Do you know any readings on the subject?

 

[Tim1]

I have never heard of a limit on number of indexes on SQL server. Basically, so long as you can create a unique name for each one, it should allow you the index.

However, it is never a very good idea to have a lot of indexes, as every time you update the base table, SQL server also has to update the indexes, and if you have many, the overhead will make your updates and inserts very slow.

If you want more than 5% of the data returned, or if there are less than 4 possible values for a field, it is really worth creating an index??

Tim

 

[tlbroadbent]

You can have 1 clustered index and up to 249 non-clustered indexes per table in SQL 7. Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

Both SQL 7 and SQL 2000 allow 249 nonclustered and 1 clustered index per table. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.