SQL Server Performance

Converting Non-Clustered Primary Key to Clustered

Discussion in 'T-SQL Performance Tuning for Developers' started by mysql2000, Aug 16, 2005.

  1. mysql2000 New Member


    I have a primary key defined as a non-clustered index and is used to enforce FK references. When trying to flip the non-clustered to clustered in EM I get the message "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option".

    When I try to delete the index I get the message "An explicit DROP INDEX is not allowed <tablename><constraint>. It is being used for PRIMARY KEY constratint enforcement".

    Any idea how I can get around this without having to drop all FK references.
  2. ranjitjain New Member

    Through EM,
    Goto design Table & Manage Indexes.
    Change the created non-clustered index to clustered index by checking create as clustered checkbox.
    I think it should run.
    It will then save the referenced table too.
  3. satya Moderator

  4. FrankKalis Moderator

    So you apparently also have a clustered index defined on other column(s)?
    If there aren't too many rows in the table, I would use EM to identify the clustered index and drop it and then change your PRIMARY KEY to clustered as Ranjit already suggested.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. mysql2000 New Member

    Ranjit, I had tried what you suggested but got the error mentioned in my original query.

    Frank, there is no other clustered index on the table.
  6. FrankKalis Moderator

    Are you sure? When you don't explicitely create your PRIMARY KEY as nonclustered, SQL Server will make it the clustered index. Can you post the table structure along with indexes, constraints...?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page