Add identity field to an existing table, change clustered primary key to non-clustered ,and created a clustered index on the new identity field

  • 1) ALTER TABLE TableX ADD FieldA indentity(1,1)

    2) Drop the existing clustered index first (IX_TableX_FieldB):

    DROP INDEX TableX.IX_TableX_FieldB

    3) Create a (temporary) UNIQUE constraint on the unique fields referenced in the primary key

    ALTER TABLE TableX

    ADD CONSTRAINT UQ_TableX UNIQUE(FieldA)

    4) Drop the PRIMARY KEY

    ALTER TABLE TableX

    DROP CONSTRAINT PK_TableX

    5) Recreate the PRIMARY KEY as CLUSTERED

    ALTER TABLE TableX

    ADD CONSTRAINT PK_TableX PRIMARY KEY CLUSTERED(FieldA)

    6) Drop the temporary UNIQUE constraint

  • Could you clearly state your question in your post please?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    I need to:

    1) Add a new identity field on table TableA. TableA has a composite Primary key

    2) Change the clustered primary key to non-clustered

    3) Create clustered index on the new identity field

    I need the solution that will take the shortest time to run.

    Find the table below:

    CREATE TABLE [dbo].[TableA](

    [TableAGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TableA_TableAGUID] DEFAULT (newid()),

    [QueryGUID] [uniqueidentifier] NOT NULL,

    ....

    [CreatedOnDate] [datetime] NOT NULL

    CONSTRAINT [PK_TableA_1] PRIMARY KEY CLUSTERED

    (

    [TableAGUID] ASC,

    [QueryGUID] ASC

    )

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_MessageType]

    GO

    ALTER TABLE [dbo].[TableA] WITH CHECK ADD CONSTRAINT [FK_TableA_Query] FOREIGN KEY([QueryGUID])

    REFERENCES [dbo].[Query] ([QueryGUID])

    GO

    ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_Query]

    ALTER TABLE dbo.TableA

    ADD TableAID IDENTITY(1,1)

    GO

    DROP INDEX [_dta_index_TableA_1] ON [dbo].[TableA]

    GO

    ---Temporary

    ALTER TABLE dbo.TableA

    ADD CONSTRAINT UQ_TableA_1 UNIQUE(TableAGUID,QueryGUID)

    GO

    ALTER TABLE dbo.TableA

    DROP CONSTRAINT PK_TableA_1

    GO

    ALTER TABLE dbo.TableA

    ADD CONSTRAINT PK_TableA_ID PRIMARY KEY CLUSTERED (TableAID)

    ON PRIMARY

    GO

    ALTER TABLE dbo.TableA

    DROP CONSTRAINT UQ_TableA_1

    GO

    CREATE NONCLUSTERED INDEX [_dta_index_TableA_1] ON [dbo].[TableA]

    (

    [QueryGUID] ASC,

    [CreatedOnDate] ASC

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply