SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maximum No. of Indexes


Maximum No. of Indexes

Author
Message
Andrew Watson-478275
Andrew Watson-478275
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 2753
I do have the 2008 BOL on my desktop. CREATE INDEX is OK, but it's wrong in the "Implementing Indexes" section of "Designing and Implementing Structured Storage".
Mauve
Mauve
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3206 Visits: 2065
Andrew Watson (3/20/2009)
I do have the 2008 BOL on my desktop. CREATE INDEX is OK, but it's wrong in the "Implementing Indexes" section of "Designing and Implementing Structured Storage".

Likewise. I have the January 2009 edition of the SQL Server 2008 BOL. In the section titled "Index Creation Tasks"
(local URL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/b182c8be-581e-4ec8-86d2-31557ee05b25.htm)
it still states the SQL Server 2005 values.

Interesting, the entire section has been eliminated/reworked in the February, 2009 edition -- which is only available online at: http://msdn.microsoft.com/en-us/library/ms180857.aspx

Edit: It is still wrong there: http://msdn.microsoft.com/en-us/library/ms190197.aspx

So depending upon what Microsoft material you reference, there are two different answers. However, the value 250 (1 clustered and 249 nonclustered) was NOT one of the answers. So if you responded 249 you are still wrong.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3510 Visits: 3059
Interesting QOD. It's good to know that SQL 2008 has raised a limit that some may have come up against. Also interesting that (as of this posting), the responses look about as close to a guess-fest as possible:
249 -- 21%
255 -- 28%
1000 -- 24%
1024 -- 27%
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12414 Visits: 6903
Another link for 249. Fortunately I will never need anywhere near that number.

http://msdn.microsoft.com/en-us/library/ms190197.aspx

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
BudaCli
BudaCli
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1536 Visits: 598
Nice and simple

What you don't know won't hurt you but what you know will make you plan to know better
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25306 Visits: 12488
nice simple question.

But I'm worried that MS thought it useful to increase the number of non-clustered indexes allowed on a table from 248 to 999: that presumably means that they saw a requirement for the bigger number. My gut feeling is that any table that has 250 or more indexes is probably a symptom of appallingly bad design - so I must be missing something here.

Tom

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18371 Visits: 12426
Tom.Thomson (10/1/2010)
nice simple question.

But I'm worried that MS thought it useful to increase the number of non-clustered indexes allowed on a table from 248 to 999: that presumably means that they saw a requirement for the bigger number. My gut feeling is that any table that has 250 or more indexes is probably a symptom of appallingly bad design - so I must be missing something here.

The increased maximum number of indexes coincided with the introduction of sparse columns (allowing up to 30,000 columns per table).
That being said, I think a design with a 30,000 column table is as bas as a design with 999 indexes on a table. So I can't tell you what business need drove the decision to increase the number of indexes, but I bet it's the same bisiness need that also increased the number of columns.

Another factor is the introduction of computed indexes. While that is a very legitimate reason for having more indexes, I still think 249 would be way enough. At least, on a "normal" table (one that doesn't even come near the old maximum of 1,024 columns).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search