Why 249 is the limit on NonClustered indexes?

Question

Why 249 is the limit on NonClustered indexes?

haresh.c1

Newbie

Points: 8
More actions
September 29, 2008 at 1:20 pm

#96747

Why is there a limit of creating 249 nonclustered indexes in SQL Server2000?

Viewing 6 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Probably because the data type used for the indexId is a tinyint, hence only goes up to 255. 0 is the heap, 1 is the cluster, 255 is the blob. There's a couple more reserved iirc

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 2

Gail is correct, but just curious... you need more than 249 indexes on a single table? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Jeff Moden (9/30/2008)
Gail is correct, but just curious... you need more than 249 indexes on a single table? 😛

It's actually not hard to hit on larger tables, seeing as automatically created column statistics also take an index ID. Add in sparse columns and filtered indexes and we'll probably be seeing that limit 'exceeded' more often

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

haresh.c1 Newbie Points: 8 More actions · Answer 4

Hi Gail

I checked datatype of indid in sysindexes is small int which permits Integer data from (-32,768) through (32,767).

so there has to be some other reason.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

I think that it was a tinyint in SQL 7 or earlier. I know it was at one point.

It's a full int in SQL 2008 (at least if you look at the system tables directly)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass