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?

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

AFAIK they are just arbitrary limits that few if any people will ever encounter in practice. Presumably they need to set some maximum limit so they know how much space to allocate in their internal structures. Maybe they just decided that decimal(3,0) would be plenty sufficient to store the indexid!

If they had allowed 1000 in SQL Server 2008 would you then be asking why not 1001?

Well, for SQL Server 2005 and earlier..

• 0 = heap. Every table has at least on entry in sys.indexes even if it has no indexes
• 1 = clustered
• 255 = for SQL Server 2000 and earlier for LOB columns (can't recall why now)

So immediately you had a maximum of 253 NC indexs (2 to 254).

Rounding? Or some legacy SQL Server 7.0/6.5/6.0/4.2 reason?

An early version of SQL Server would have used a tinyint field for the index id. Tinyint has a maximum value of 255. The design team may have rounded this down to 250 to make it easy to remember (as they did with the 8000 character limit for varchar fields). The 250 indexes are split: one clustered index, and 249 non-clustered indexes.

It is basically an internal implementation limitation. It is not driven by the metadata size (ie. a tinyint column or a small int column for index_id), but instead is the metadata column that reflects the internal limitation.

Whenever such a limitation is surfaced, it means that somewhere in the code there are practical limits of how this is handled. To give an example, perhaps query plan generation would get too complex if it would have to consider tens of thousands of indexes on the same table and it would take way more time to produce even a trivial plan. When faced with such issues, a line is drawn at what is deemed as a 'reasonable' number. ~250 indexes seemed reasonable in the late 90s, the limit was pushed to 1000 in R2.