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