SQL : NonClustered Index error

Business Central/NAV Technical Forum
1

Hi,

Hopefully someone can help.

We are adding two new keys to the item table (Nav360) and when importing the object into our LIVE database we get the error;

Server: Msg 1910, level 16, State 1
Cannot create more than 249 nonclustered indices or column statistics on one table.

The strange thing is is that the same keys can be added Ok to a copy of the Live system.

The keys in total are;

No.
Search Description
Inventory Posting Group
Shelf/Bin No.
Vendor No.
Gen. Prod. Posting Group
Low-Level Code
Production BOM No.
Routing No.
Vendor Item No.,Vendor No.
Common Item No.
Manufacturing Spec. No.
Base Item No.
Item Kind
Packaging Spec. No.
Sales Product Type,Sales Product Group
Item Category Code
Sales Product Group
Print Spec. No.
Bespoke Item Flag,Bespoke Item Customer
Product Group Code,No.
External Agency (Customer)

Any ideas?

Thanks in advance,
Tim

2

Note that the error message speaks of a limit of 249 nonclustered indices or column statistics per table.

If you run ‘sp_statistics’ on the Item table, I suspect you will find that a large number of column statistics have been created for the table, in addition to the defined indexes.

Column statistics can be created automatically by SQL server as part of its query optimization, or someone may have run ‘sp_createstats’ on the database for some reason.

You will probably need to use DROP STATISTICS … to reduce the number of column statistics on the table, in order to add the two new keys.

Hope this helps.

3

These “strange” statistics are created when the db option “Auto. Create Statistics” is enabled. I recommend to disable this opion (and “Auto. Update Statistics”) and maintain the statistics by a SQL Server Agent Job, executing this code:

exec sp_updatestats

exec sp_createstats ‘indexonly’

4

Thanks to all. It appears that statistics get created for fields (columns) automatically when users try to filter on any field. I “dropped” these additional stats and all was well.

Regards, Tim