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: http://sqlblog.com/blogs/hugo_kornelis