Error 1910

Severity Level 16

Message Text

Cannot create more than %d indexes 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