TSQL Alter PRIMARY KEY Cluster Index MSSQL2008r2

Question

is it possible to ALTER a PRIMARY KEY CLUSTERED Index on an existing table without losing the data?

If so, what is the ALTER command for this please?

EDIT I want to add an additional column to the PRIMARY KEY CLUSTERED Index

Thanks

what are you trying to alter? The columns that make up the primary key? — Kritner, Sep 18 '14 at 14:03
I want to add an additional column to the PRIMARY KEY CLUSTERED Index — Mazhar, Sep 18 '14 at 14:05
No, it's not possible to directly change a constraint definition. `ALTER TABLE` only allows you to add or remove a constraint, not change its definition. Also, constraints are backed by indexes. If you look at [`ALTER INDEX`](http://msdn.microsoft.com/en-gb/library/ms188388.aspx) you'll see that there's no way to change the columns included in an index. But removing and adding a constraint shouldn't cause you to lose the actual data. — Damien_The_Unbeliever, Sep 18 '14 at 14:10

score 1 · Accepted Answer · edited May 23 '17 at 12:29

Here is what I've done in the past to change a primary key on a table:

BEGIN TRANSACTION doStuff

DECLARE @isValid bit
SET     @isValid = 1

DECLARE @pkName varchar(50)
SET     @pkName = (
    SELECT TOP 1 name
    FROM sys.key_constraints
    WHERE type ='pk'
        AND OBJECT_NAME(parent_object_id) = N'TableName'
)

DECLARE @sql nvarchar(2000)
SET @sql = N'
ALTER TABLE dbo.TableName
DROP CONSTRAINT ' + @pkName

EXEC (@sql)

IF (@@ERROR <> 0)
    BEGIN
        PRINT 'Error deleting primary key'
        SET @isValid = 0
    END

ALTER TABLE dbo.TableName
ADD PRIMARY KEY (primary key columns separated by comma)

IF (@@ERROR <> 0)
    BEGIN
        PRINT 'Error creating primary key'
        SET @isValid = 0
    END

IF (@isValid = 1)
    BEGIN
        PRINT 'Commit'
        COMMIT TRANSACTION doStuff
    END
ELSE
    BEGIN
        PRINT 'Rollback'
        ROLLBACK TRANSACTION doStuff
    END

Note as pointed out in: Best way to change clustered index (PK) in SQL 2005 this will reorder the data in your table throughout the operation, so depending on the size of the table it could take a significant amount of time.

Thanks for this. How would this affect the Non Clustered Indexes on the same table? Would they REBUILD at the same time or something? — Mazhar, Sep 18 '14 at 14:14
http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/ "any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created." — Kritner, Sep 18 '14 at 14:20

TSQL Alter PRIMARY KEY Cluster Index MSSQL2008r2

1 Answers1