3

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?

Amro
  • 123,847
  • 25
  • 243
  • 454
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
  • What is the interest of that question ? – iDevlop Nov 22 '10 at 11:51
  • @iDevlop As I am novice in Sql Server 2005 and I got this question in mind while reading a tutorial on internet. I always believe that stackoverflow is one of the best place to ask technical question. Might be I'll never create 249 non clustered index on a table in my entire life but it doesn't harm knowing the reason. Because as a learner I always kept this thing in my mind that "There is nothing without any reason". There must be some reason. – Zerotoinfinity Nov 22 '10 at 11:56
  • Well, if you spend here your earnings, then extend the question to 999 in SQL Server 2008 because some answers explain it by tinyint capacity (0-255) of indid – Gennady Vanin Геннадий Ванин Nov 29 '10 at 19:53

5 Answers5

5

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

Gennady Vanin Геннадий Ванин
  • 10,136
  • 11
  • 75
  • 106
  • This is something helpful. So I understand that if I have created 1 clustured index on a table I can now create only 3199 - 1 = 3198 non clustered index. and after that if we create any index it will come under XML indexes. – Zerotoinfinity Nov 30 '10 at 08:44
  • So the actual limit of non-clustered index in sql server 2008 is 3198 and not 999? – Zerotoinfinity Nov 30 '10 at 08:45
  • The actual limit depends on SQL Server version as I wrote. It is 999 in SQL Server 2008 R2, 249 in 2005. Compiler will give you error if you exceed it. That's simple. I do not think that 2198 is also limit. Would it be necessary, the used datatypes and numbers could have been adjusted. It is just implementation. No more, no less – Gennady Vanin Геннадий Ванин Nov 30 '10 at 11:41
  • Thanks vgv8. I got my answer. – Zerotoinfinity Dec 01 '10 at 07:02
2

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?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

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?

gbn
  • 422,506
  • 82
  • 585
  • 676
1

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.

Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
  • 1
    In SQL Server 2005 indexids were 0 for a heap 1 for a clustered index 2-250 for non clustered index and the rest of the ids reserved for internal use. – Martin Smith Nov 22 '10 at 13:06
1

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569