Replace the default clustered index with a non-primary/non-key attribute

I have a request to replace the default clustered index with a non-primary attribute and I have an idea of how to simply drop an index and just make another one but I have no clue how to replace the default clustered index.

This is as far as I got.

CREATE INDEX newCustomerIndex ON CustomerInfo(fullname, homeAddress);

DROP INDEX CustomerInfo.PK__Customer__B611CB9D24135D51

However whenever I execute this piece of code I get this error

An explicit DROP INDEX is not allowed on index ‘CustomerInfo.PK__Customer__B611CB9D24135D51’. It is being used for PRIMARY KEY constraint enforcement.

Answer

There are definitely times when you want the Clustered Index to be separate from the Primary Key – particularly when you search in ‘ranges’.

For example, on log tables, I’ll frequently have the primary key as an Identity (auto-incrementing) integer so each row is uniquely identified, but I’ll put the clustered index on the date/time stamp field. This is because most of the reads etc will be within a date range and therefore the clustered index can be used directly. However, you cannot guarantee each date/time stamp is unique so you cannot use it as a primary key – and you can only have 1 clustered index on a table.

Note that the below is based on SQL Server – as per your tags. However, I’m not sure if the fields you are referring to are the current PK, or the ones you want to have as your clustered index. Therefore, I’ll write this for a fictional table called ‘dbo.Temp’ with ‘Auto_Id’ and ‘Log_datetime’.

I assume you already have the primary key which is also the clustered index. The typical process is as follows.

1. DROP and CREATE the current primary key, but as a non-clustered index rather than clustered
2. Create your new clustered index

However, you will also need a preceding and following step – identifying and managing the foreign key constraints. I’ll get to this last.

To do step 1, I will usually open the table on the left (object explorer), open ‘Indexes’, then right-click on the index and script index -> ‘DROP and CREATE to

This gives some code similar to the following

ALTER TABLE [dbo].[Temp] DROP CONSTRAINT [PK_Temp] WITH ( ONLINE = OFF )
GO

ALTER TABLE [dbo].[Temp] ADD  CONSTRAINT [PK_Temp] PRIMARY KEY CLUSTERED 
(
    [Auto_ID] ASC
)
GO

I make one change to this before starting – change the new constraint to be NONCLUSTERED rather than clustered e.g.,

ALTER TABLE [dbo].[Temp] DROP CONSTRAINT [PK_Temp] WITH ( ONLINE = OFF )
GO

ALTER TABLE [dbo].[Temp] ADD  CONSTRAINT [PK_Temp] PRIMARY KEY NONCLUSTERED    -- modified here
(
    [Auto_ID] ASC
)
GO

I then add my create index statement to create the index e.g.,

CREATE CLUSTERED INDEX CX_Temp ON dbo.Temp ([Log_Datetime], [Auto_ID])
GO

And then run.

Sometimes this won’t work – especially when the index is being used for foreign key constraints (e.g., another table has a foreign link to this table/current PK) – therefore you cannot drop the PK, nor any of the following steps.

Therefore you need to find the relevant foreign key constraint. Once found (which can be annoying), I then usually do a similar thing to script the index deletion and re-creation (right-click on the foreign key, script it as ‘DROP and CREATE to’).

You then copy the first part (dropping the foreign key) before the script above, add the re-creating of the foreign key to the end. This therefore

• disables the foreign key constraint
• deletes/recreates the primary key as a non-clustered index
• creates the new clustered index
• recreates the foreign key constraint