There is an install process that creates a new clean instance of our database, then populates some of the tables with default data. Following database creation, the data is copied from the old database to the new. One table in particular has no default data and is empty following the database creation phase. That table has 267 columns in it (combination of char, varchar, int, tinyint). I have 4 indices that I need to add to the table, but only one will add, and it has only 6 columns. When I attempt to add more indices, I get the following error...
"Cannot create more than 249 nonclustered indices or column statistics on one table".
Currently, the table has only one index and I cannot add more. In the prior version of the database, I was able to add all 4 indices. At most, there are only 4 or 5 new fields in this version. I am out of ideas on what to do. Can anyone help?
Step 1: look at any table with over 200 columns as a candidate for normalization.
Step 2: Turn off Auto-Create statistics on the database.
Step 3: Add your indices.
Step 4: Turn Auto-Create statistics back on.
What likely happened was that SQL Server automatically created statistics for 248 of the 267 columns, because they showed up in a where clause of some query that was run. Turning off Auto-Create Statistics should remove the statistics, then you should be able to run the index scripts with no problems. Good luck.
1. it is a simple columnar table with no triggers and only one stored procedure and UDF for reporting.
2. sp_depends shows no dependancies other than the UDF mentioned above
3. tried the AUTO_CREATE_STATISTICS database option... no dice... still giving me the same error. I went to Query Analyzer, copied the code I am using to create the indices there. Then added the Alter database ... AUTO_CREATE_STATISTICS on/off. Then removed all existing indices (only one there), then ran the script. It bombed with the same error
You're right!! I took that script, modified it to create Play2 and add the indices to it, and it ran flawlessly. So, there must be something else weird in the way the install script is creating that table. I'll have to mention this to the guy who is managing the database layout and building the install CDs.
It may actually work if I use that script to recreate the Play table. Simply drop the table after the install creates it, then use the script to recreate it. I'll bet that will work.
After reviewing the script, you could probably change the [PlayMaint1] index to a clustered index. If nothing else, this will get you one index closer to your goal, since you are allowed one clustered in addition to 249 non-clustered indexes.