Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Maximum No. of Indexes Expand / Collapse
Author
Message
Posted Friday, March 20, 2009 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 1,208, Visits: 2,197
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
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: Thursday, July 24, 2014 11:03 AM
Points: 1,182, Visits: 1,970
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
Posted Friday, March 20, 2009 2:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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
Posted Monday, March 30, 2009 10:36 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 4,009, Visits: 6,072
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
Posted Wednesday, January 6, 2010 5:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
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
Posted Friday, October 1, 2010 7:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,563, Visits: 9,063
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
Post #997133
Posted Saturday, October 2, 2010 3:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 5,925, Visits: 8,175
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 ««12

Permissions Expand / Collapse