Solution for SQL Server "Cannot create more than N nonclustered indices" Error
Here's a somewhat annoying problem I've run into to a few times in SQL Server 2000, so I thought I'd type up a solution that has worked for me (note: I'm not sure if this same problem exists in SQL Server 2005, so everything in this post relates to SQL 2000):
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:
Cannot create more than 249 nonclustered indices or column statistics on one table.
You may be frustrated by this initially because you know for a fact that there are not 249 indices on this table (or maybe you do...but you've got bigger problems in that case). What's really blocking you, though, are not too many indices, but too many auto-computed statistics, which are like temporary indices created by SQL Server for columns that don't have indices. What you have to do to add your new index is get rid of one of these auto statistics. Here's how to do that:
For some reason, the "Manage Statistics" dialog box is in SQL Query Analyzer, not Enterprise Manager. Go to the Tools menu in Query Analyzer and choose Manage Statistics. Choose the database and the table, then scroll down to select a "statistic" from the list for a column you don't care about (that is, one that is seldom or never used in a WHERE clause). Use the Delete button to delete it.
Now try adding your new index again. It should work.
Note: one step in the above instructions that I may have skipped is to go to the Properties dialog for the database in Enterprise Manager and turn off Auto Compute Statistics and Auto Create Statistics. When I solved this for myself, I had turned these off, and then I turned them back on after I deleted a statistic and added the new index. I'm hoping, though, that this step is not necessary. Please post your findings as a comment to this thread.
This is just one of many, many good reasons to not create tables with columns numbering in the hundreds.
Hope that helps,
Dan
auto statistics = ON
Auto update/create statistics where ON and this fix worked for me. Thanks for posting it.
Yet another feature?
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.