Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sriram » Maximum No. of Indexes
Add to briefcase
17 posts, Page 2 of 2
««12

Maximum No. of Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Andrew Watson-478275
Posted Friday, March 20, 2009 7:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953, Visits: 1,875
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".
Post #680380
Mauve
Posted Friday, March 20, 2009 12:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:43 AM
Points: 1,146, Visits: 1,848
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.
Post #680637
john.arnott
Posted Friday, March 20, 2009 2:00 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
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%
Post #680689
The Dixie Flatline
Posted Monday, March 30, 2009 10:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:02 PM
Points: 3,788, Visits: 5,543
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? -- Stephen Stills
Post #686297
BudaCli
Posted Wednesday, January 06, 2010 5:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 9:02 AM
Points: 1,046, Visits: 573
Nice and simple

What you don't know won't hurt you but what you know will make you plan to know better
Post #842696
L' Eomot Inversé
Posted Friday, October 01, 2010 7:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 7,096, Visits: 7,156
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #997133
Hugo Kornelis
Posted Saturday, October 02, 2010 3:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 5,237, Visits: 7,044
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
Post #997179
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.