Non Clustered Index

Question

Non Clustered Index

aprasy

Mr or Mrs. 500

Points: 511
More actions
July 16, 2009 at 6:04 am

#212092

Hi ,
Usually the table have 249 non clustered index. I would like to know why there is such kind of restriction to have index. Why dont we have more than 249 index in table. Could anyone help me for this question. This was the question asked by one interviewer to me.
Thanks
Prasanna

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

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

As far as I know, it's just a limit. In SQL Server 2008, the limit is 999.

Was he asking why you would not want that many indexes? Or was he asking why is there a limit? Or was he asking why is that the limit?

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

steveb. SSC-Forever Points: 46733 More actions · Answer 2

Though that may be the max allowed limit in SQL server the pratical limit would be a whole lot lower, I don't think i have ever seen a table coming even close to 249 indexes

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Exactly. That's why I was trying to clarify the question asked. If I saw more than 4 or 5 indexes I think I'd be quite concerned. 249 would positively freak me out.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Agreed, though I have seen a case where someone got an error when trying to create an index because that limit had been hit. Table was a denormalised mess with about 400 columns in it, but only had 5 indexes on it (cluster and 4 nonclustered). Anyone want to take a guess why they were getting a 'too many indexes' error?

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 5

Grant, sorry to to inform you but I started a new job about 4 months ago and 12 or so tables had over 15 indexes on (one had 33). This was actualy setup by a experiance contract DBA! That what taking dmv....misssing_idexes literally does for you, before you change the SP's

Im in the process of normalizing the DB and I've got most of them down to 3, the one that had 33 is not down to 4.

It was very painful re-writting the SP's etc, but will be worth it in the long run!

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

GilaMonster (7/16/2009)
Agreed, though I have seen a case where someone got an error when trying to create an index because that limit had been hit. Table was a denormalised mess with about 400 columns in it, but only had 5 indexes on it (cluster and 4 nonclustered). Anyone want to take a guess why they were getting a 'too many indexes' error?

...Not sure... Oh, were they trying to create another clustered index?

I did that once on Sybase... ooh, maybe 13 years ago. I was using a third party tool, it might have been DBArtisan. I created a clustered index, liking the performance, I decided to add another, not knowing what the hell I was doing, but I was able to because of a glitch. It didn't do the table much good to have two clustered indexes on it though.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Grant Fritchey (7/16/2009)
GilaMonster (7/16/2009)
Agreed, though I have seen a case where someone got an error when trying to create an index because that limit had been hit. Table was a denormalised mess with about 400 columns in it, but only had 5 indexes on it (cluster and 4 nonclustered). Anyone want to take a guess why they were getting a 'too many indexes' error?
...Not sure... Oh, were they trying to create another clustered index?

Nope. Noncluster. This was SQL 2000.

Hint:

SELECT count(*) FROM sysindexes WHERE id = OBJECT_ID('TableInQuestion') AND name LIKE '_WA_SYS%'

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

GilaMonster (7/16/2009)
Nope. Noncluster. This was SQL 2000.
Hint:
SELECT count(*) FROM sysindexes WHERE id = OBJECT_ID('TableInQuestion') AND name LIKE '_WA_SYS%'

Ah.... Cool. I hadn't thought of that.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software