5

In the past, I have chosen the 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]
GO
  • How big is the table (ie number of rows, and total size on disk), and do you have a decent maintenance window, or is this table accessed 24x7? – Max Vernon Oct 6 '15 at 18:00
  • Keep in mind that you can cluster on Created (to support range scans) but have your PK on ID (to support referential integrity). – Jon of All Trades Jul 19 '16 at 21:12
3

Just drop the existing clustered index, then create a new one on the ID column.

DROP INDEX [Created] ON dbo.Realty;
GO

CREATE UNIQUE CLUSTERED INDEX CX_Realty ON dbo.Realty (ID);
GO

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 WITH (ONLINE=ON) to the statement.

  • The one downside here is that the table will now be left with a clustered index on (ID) and another non-clustered index (the primary key) on (ID). While there could be situations where this (narrower) duplicate index is a good idea, I don't get the sense that it's what the OP really wants in this case. – Geoff Patterson Oct 6 '15 at 17:54
  • Good point, @GeoffPatterson - I was working on a solution using ALTER TABLE ... SWITCH to avoid the issue; however it will require making two copies of the data in the table to get around the IDENTITY() column. I didn't want to propose a solution that is overly complex. – Max Vernon Oct 6 '15 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.