Post reply

Indexes

  • October 6, 2011 at 8:31 am

    #1391228

    Thanks for the question. I would like to see a table that has needed that many indexes.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 6, 2011 at 8:58 am

    #1391247

    SQLRNNR (10/6/2011)

    Thanks for the question. I would like to see a table that has needed that many indexes.

    I'd guess Microsoft increased this number with the introduction of filtered indexes in SQL Server 2008...

    Still wouldn't want to have a table with this many indexes in a real application scenario.

  • October 6, 2011 at 9:07 am

    #1391253

    michael.kaufmann (10/6/2011)

    SQLRNNR (10/6/2011)

    Thanks for the question. I would like to see a table that has needed that many indexes.

    I'd guess Microsoft increased this number with the introduction of filtered indexes in SQL Server 2008...

    Still wouldn't want to have a table with this many indexes in a real application scenario.

    I'm thinking along the same lines - that could become a nightmare real quick.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 6, 2011 at 9:14 am

    #1391260

    I would like to see a real world scenario where 999 indexes are needed. Seems like a nightmare to maintain...

  • October 6, 2011 at 9:14 am

    #1391261

    Like other have already stated, this was a great straight-forward question. That's excellent and I congratulate the author for this one.

    I also agree that 999 on a single table would be a lot of indexes. I suppose there's someone out there that's complaining they can't add one more though <smile>.

  • October 6, 2011 at 9:31 am

    #1391273

    damn, i needed 1000:exclamationmark:

    hahaha

  • October 6, 2011 at 10:00 am

    #1391290

    This one looked familiar. Back in January, we were asked how many indexes could be applied to a SQL2005 table. Thanks for the reminder, not so much what the limits may be but, for the fact that there are limits.

  • October 6, 2011 at 11:51 am

    #1391360

    michael.kaufmann (10/6/2011)

    Got it right--for SQL Server 2005 😉

    Thanks for a straightforward question.

    And you might find this link helpful as well.

    Regards,

    Michael

    I recommend Michael's link as well.

    In SQL Server 2005 and earlier, a maximum of 249 nonclustered indexes could be created on a table.

    In SQL Server 2008: a maxiumum of 999 nonclustered indexes can be created on a single table.

  • October 6, 2011 at 11:59 am

    #1391368

    john.arnott (10/6/2011)

    This one looked familiar. Back in January, we were asked how many indexes could be applied to a SQL2005 table. Thanks for the reminder, not so much what the limits may be but, for the fact that there are limits.

    completly agree with you, sometimes limits are way beyond a user may or should need, but it's good to know there are in fact some limits...

  • October 6, 2011 at 12:23 pm

    #1391390

    I have to wonder what having so many indexes would to to insert and update performance

  • October 7, 2011 at 11:56 am

    #1391905

    May be in Denali, this limit will be further increased

    ===========================================
    Better try and fail than not to try at all...

    Database Best Practices[/url]

    SQL Server Best Practices[/url]

  • October 10, 2011 at 2:02 am

    #1392283

    Nice basic question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 10, 2011 at 2:20 am

    #1392287

    Nice straightforward question and answer.

    Tom

Viewing 13 posts - 16 through 27 (of 27 total)

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