July 22, 2010 at 4:10 am
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
July 22, 2010 at 6:17 am
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
July 22, 2010 at 7:22 am
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