Maximum No. of Indexes

  • 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

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 2 posts - 16 through 16 (of 16 total)

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