Reason for the count of non clustered index in Sql Server
Why we have 249 non clustered index in sql server 2005? why not 240 or 300? and the same question for sql server 2008, why 999 ? Why not 800 or 1000?
Answer
They are making pretty (rounded) digits...
SQL Server 2005: 1 Clustered Index + 249 Nonclustered Indexes = 250 Indexes per table
SQL Server 2008: 1 Clustered Index + 999 Nonclustered Indexes = 1000 Indexes per table
Update:
You should have asked why 999 in SQL Server 2008.
This had been explained in answer to my question. This increase was explained by introduction of filtered indexes in SQL Server 2008.
The datatype of index_id in sysindexes means:
- 0 for heap
- 1 - clustered index
- >1 nonclustered
- >=3200 - XML indexes
So, we can still observe increase up to 3198(3199-1) in future versions of SQL Server.
I thought previously that sys.indexes is synonym to sysindexes but I found just now that they are different, sysindexes has indid (instead of index_id) and does not contain rows for XML indexes!
index_id from sys.indexes has type int(4bytes) and indid from sys.sysindexes has type smallint (2bytes) (SQL Server 2008, probably increased from previous versions)
I found helpful and interesting the article Tibor Karaszi. Key count in sys[.]indexes