Non-clustered Index - Why the limit of 249

Question

Post reply

Non-clustered Index - Why the limit of 249

ARUL-285958

Ten Centuries

Points: 1081
More actions
May 23, 2006 at 7:47 am

#115215

Hi DBA's,
I had the below question in the Microsoft technical interview.
Why a table has only 249 non-clustered index and not more than that?
I had no clue..but would like to know if someone could answer the same... 🙂
Thanks !
Get busy living ....or get busy dying....

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

gallan-229957 SSCommitted Points: 1520 More actions · Answer 1

Wrong forum, this is the Notification Services forum.

Kayode Grasshopper Points: 18 More actions · Answer 2

Each PRIMARY KEY and UNIQUE constraint generates an index. The number of UNIQUE and PRIMARY KEY constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

The question is WHY?.. and for one I'd like to know to .

twillcomp SSC Eights! Points: 989 More actions · Answer 4

From BOL (searching for Table and Index Architecture)

"Each table has a set of rows in sysindexes:

A heap has a row in sysindexes with indid = 0.

A clustered index on a table or view has a row in sysindexes with indid = 1.

Each nonclustered index created for a table or view has a row in sysindexes.

The values for indid in the rows for each nonclustered index range from 2 through 250.

Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255. "

Explains why the 249 limit but doesn't really explain why have a limit or does it?

ARUL-285958 Ten Centuries Points: 1081 More actions · Answer 5

That's exactly what was asked by Microsoft.

WHY is the limitation ??? There should be some LOGIC....

Hope to get more clue on to it....

By the way... I already got into the JOB in MS 🙂

Shud thank ALL you folks as well for the help !!!

Get busy living ....or get busy dying....

CK Bhatia Say Hey Kid Points: 679 More actions · Answer 6

Funny thing is that the indid column in sysindexes is defined as a smallint, thus they could have gone up to 32,767.

I bet it has something to do with using 8 bits somewhere since 2 to the power 8 = 255.

But now that you are in MS - Please do let us know what you find out.

I feel the need - the need for speed

CK Bhatia

d viz Ten Centuries Points: 1092 More actions · Answer 7

This is more of a rhetorical question. The 249 limit obviously has to do with a smallint datatype. BUT I can't imagine why a normalized database would need that many indexes!

Brandon Thetford SSC Journeyman Points: 86 More actions · Answer 8

Smallint is 16 bits, d_vis. Perhaps you are thinking of tinyint?

I partially agree, though. What need could there possibly be, on a single table, for more than 249 indexes? Seems to me there is a normalization problem somewhere or just a bunch of unneeded indexes.

Remember that indexes have diminishing returns, the more of them you add, if you are constantly inserting and updating data on your table.

I also partially agree with the other argument on here, though. Why is there a limit in the first place? Seems odd to artificially impose limits like that if there is no reason other than lack of foresight ("millennium bug," anyone?).