developer.* Blogs - Solution for SQL Server "Cannot create more than N nonclustered indices" Error - Comments

auto statistics = ON

damdave1 — Thu, 15 Feb 2007 07:20:07 -0800

Auto update/create statistics where ON and this fix worked for me. Thanks for posting it.

Yet another feature?

Mario Van Damme — Tue, 20 Jun 2006 04:31:15 -0700

The 'Auto create statistics' and 'Auto update statistics' look very harmless seen from a distance. Naturally, why would you want to assist the RDBMS in doing its work? One important reason though is performance : If your database server is not or less used after business hours, it is better to schedule the creation and update of the statistics manually.
This way, performance is not hurt when these statistics are being created.

For the record : I don't think you have to turn off the auto creation and update of the statistics, but internally the auto-creation feature will run against the same problem. This implies ofcourse that the optimizer will no longer create statistics for new columns for which no statistical data is available.

Solution for SQL Server "Cannot create more than N nonclustered indices" Error

Daniel Read — Fri, 16 Jun 2006 14:37:44 -0700

The scenario is that you have a gigantic SQL Server 2000 table--gigantic not necessarily because it has a lot of rows, but gigantic because it has a ton of columns. I've inherited a couple tables like this on different projects, tables with two or three hundred columns. (Don't look at me, I didn't create these tables.) It's not uncommon to need to add a new index to these kinds of ridiculous tables to cover a query. So you go to try and add your new index and you get an error like this: