16
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.
Using the primary key clause in a create
table statement creates a unique clustered index.
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.
If a clustered index already exists, drop it.
Create a new clustered index.
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.
All versions
