CODEWITHSUNDEEP
t-sqlsql-server-2008-r2indexingprimary-keyclustered-index
Mazhar
Mazhar

Reputation: 3837

TSQL Alter PRIMARY KEY Cluster Index MSSQL2008r2

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

Upvotes: 0

Views: 253

Answers (1)

Kritner
Kritner

Reputation: 13765

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.

Upvotes: 1

Related Questions