Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add identity field to an existing table, change clustered primary key to non-clustered ,and created a clustered index on the new identity field Expand / Collapse
How do i Add identity field to an existing table, change clustered primary key to non-clustered ,and created a clustered index on the new identity field
Poll ResultsVotes
The database will be in use at the moment I try to change the indexes round - so my primary concern that I want to avoid, is that at some point in the process the PK constraint will not exist on the table. I want to be protected against any risk of duplicate keys being inserted.
0%
0
i.e. I can't just drop the primary key and recreate it.
0%
0
0%
0
Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Thursday, July 22, 2010 4:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:34 AM
Points: 17, Visits: 280
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
Post #957045
Posted Thursday, July 22, 2010 6:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Could you clearly state your question in your post please?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #957108
Posted Thursday, July 22, 2010 7:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:34 AM
Points: 17, Visits: 280
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


Post #957167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse