Error 1910

Error 1910

Severity Level 16

Message Text

Cannot create more than %d nonclustered indices or column statistics on one table.

Explanation

This error occurs when either:

• A table already has 250 indexes.

  Or

• A table has a large number of indexes and statistics are either being created or updated. Microsoft® SQL Server™ makes an entry in sysindexes for each set of column-level statistics that are either created or updated, if the base column does not already have an index.

The following example produces this error message by updating statistics on a table with 250 indexes:

USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'TestCols')
   DROP TABLE TestCols
GO
DECLARE @counter int, @stmt varchar(255)
SET @counter = 1
CREATE TABLE TestCols
 ( 
  c1 int IDENTITY(1, 1),
  c2 char(255)
 )
-- Creating 250 indexes.
WHILE (@counter < 251)
   BEGIN
      IF (@counter = 1)
         CREATE UNIQUE CLUSTERED INDEX Ind1 ON TestCols (c1)
      ELSE
      BEGIN
         SELECT @stmt = 'CREATE INDEX t1Ind' + LTRIM(STR(@counter)) + 
            ' ON TestCols (c1)'
         EXEC (@stmt)
      END
      SET @counter = @counter + 1
   END

-- Updating Statistics. Returns error 1910.
UPDATE STATISTICS TestCols (c2) WITH COLUMNS, FULLSCAN

Action

Either:

• Modify the CREATE INDEX statement to use no more than 250 indexes.

  Or

• Remove any unused, nonclustered indexes to create or update statistics successfully.

For more information, see CREATE INDEX.

See Also

Errors 1000 - 1999

sysindexes

UPDATE STATISTICS