Error 1902



Message text

Cannot create more than one clustered index on table '%.*s'. Drop the existing clustered index '%.*s' before creating another


Adaptive Server uses a clustered index to sort rows so that their physical order is the same as their logical (indexed) order. The bottom or leaf level of a clustered index contains the actual data pages of the table.

In a nonclustered index, the physical order of the rows is not the same as the indexed order. The leaf level of a nonclustered index contains pointers to rows on data pages.

Many nonclustered indexes are allowed on a table, but only one clustered index per table is allowed. Error 1902 occurs when you try to create a second clustered index on the table named in the error message.

NoteUsing the primary key clause in a create table statement creates a unique clustered index.


  1. List the existing indexes on the table:

    1> use <database_name>
    2> go
    1> sp_helpindex <object_name>
    2> go

    Where <database_name> is the database where the table named in the error message resides and <object_name> is the name of the table in the error message. The index_description column of the output shows whether the index is clustered or nonclustered.

  2. If a clustered index already exists, drop it.

  3. Create a new clustered index.

Additional information

See “Using drop index and create clustered index” in the most recent version of Performance and Tuning: Basics for more information about drop index and create index.

Versions in which this error is raised

All versions