patternsqlMinor
Change clustered index without dropping the primary key
Viewed 0 times
clusteredwithouttheprimarydroppingindexchangekey
Problem
In the past, I have chosen the
Now I concluded (base on the execution plan) that it would be better to choose the ID identity primary key as the clustered key, because it is referenced as foreign key very often.
I would like to drop the current clustered key and create a new, but I cannot drop the primary key, because that the
Can I just switch primary key to clustered index or do I need to drop the primary key and the chain of all dependent objects?
Bellow you will find table definition and clustered index definition.
datetime column created as inappropriate clustered index in the table. Now I concluded (base on the execution plan) that it would be better to choose the ID identity primary key as the clustered key, because it is referenced as foreign key very often.
I would like to drop the current clustered key and create a new, but I cannot drop the primary key, because that the
full-text index is dependent on that primary key. Can I just switch primary key to clustered index or do I need to drop the primary key and the chain of all dependent objects?
Bellow you will find table definition and clustered index definition.
CREATE TABLE [dbo].[Realty](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Created] [datetime] NOT NULL,
....
CONSTRAINT [PK_Realty] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
...
CREATE CLUSTERED INDEX [Created] ON [dbo].[Realty]
(
[Created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GOSolution
Just drop the existing clustered index, then create a new one on the
Of course, you'll want to do this during a maintenance window so you don't cause too much blocking. If you have Enterprise Edition you can do the CREATE INDEX operation online by adding
ID column.DROP INDEX [Created] ON dbo.Realty;
GO
CREATE UNIQUE CLUSTERED INDEX CX_Realty ON dbo.Realty (ID);
GOOf course, you'll want to do this during a maintenance window so you don't cause too much blocking. If you have Enterprise Edition you can do the CREATE INDEX operation online by adding
WITH (ONLINE=ON) to the statement.Code Snippets
DROP INDEX [Created] ON dbo.Realty;
GO
CREATE UNIQUE CLUSTERED INDEX CX_Realty ON dbo.Realty (ID);
GOContext
StackExchange Database Administrators Q#117079, answer score: 4
Revisions (0)
No revisions yet.