SQL Server – Error When Using WITH (DROP_EXISTING = ON)

index-maintenancesql serversql server 2014

Okay so I've been condensing files off of some tiny files and moving them onto an FG that I created for these miscellaneous tables. I've been moving these tables by recreating the indexes with the WITH (DROP_EXISTING = ON) option and it's been working great. However this last time it gave me an error. Here's the script I'm running

CREATE Unique CLUSTERED INDEX PK_MyTable 
    ON dbo.MyTable (MyTableID, RowStatus)  
    WITH (DROP_EXISTING = ON)  
    ON FG_Misc;

When I run this I get the following error message.

Msg 1902, Level 16, State 3, Line 1

Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index PK_MyTable' before creating another.

I don't see any reason why this is throwing this error on this particular table but not any others I've been working on.

Best Answer

This can happen when the current primary key is nonclustered, and there is also a clustered index:

CREATE TABLE dbo.MyTable
(
    MyTableID integer NOT NULL,
    RowStatus tinyint NOT NULL,
    SomeOtherKey integer,

    CONSTRAINT [PK_MyTable]
        PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus),

    CONSTRAINT [CUQ_MyTable]
        UNIQUE CLUSTERED (SomeOtherKey)
);

CREATE Unique CLUSTERED INDEX PK_MyTable 
    ON dbo.MyTable (MyTableID, RowStatus)  
    WITH (DROP_EXISTING = ON);

Msg 1902, Level 16, State 3, Line 14
Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index 'CUQ_MyTable' before creating another.

It is valid to change a primary key from nonclustered to clustered using DROP_EXISTING syntax:

CREATE TABLE dbo.MyTable
(
    MyTableID integer NOT NULL,
    RowStatus tinyint NOT NULL,
    SomeOtherKey integer,

    CONSTRAINT [PK_MyTable]
        PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus),
);

CREATE Unique CLUSTERED INDEX PK_MyTable 
    ON dbo.MyTable (MyTableID, RowStatus)  
    WITH (DROP_EXISTING = ON);
Related Question