Change a Primary Key from Nonclustered to Clustered

Change a Primary Key from Nonclustered to Clustered

1) Drop the existing clustered index first (IX_TableX_FieldB):

   DROP INDEX TableX.IX_TableX_FieldB

2) Create a (temporary) UNIQUE constraint on the unique fields referenced in the primary key

    ALTER TABLE TableX
    ADD CONSTRAINT UQ_TableX UNIQUE(FieldA)

3) Drop the PRIMARY KEY

    ALTER TABLE TableX
    DROP CONSTRAINT PK_TableX

4) Recreate the PRIMARY KEY as CLUSTERED

   ALTER TABLE TableX
   ADD CONSTRAINT PK_TableX PRIMARY KEY CLUSTERED(FieldA)

5) Drop the temporary UNIQUE constraint

   ALTER TABLE TableX
   DROP CONSTRAINT UQ_TableX

6) Add the IX_TableX_FieldB back on as NONCLUSTERED

   CREATE NONCLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)

How to change the primary key to be non-clustered?

Drop the clustered index, then recreate the primary key as non-clustered:

ALTER TABLE dbo.Config DROP CONSTRAINT PK_Config
go
ALTER TABLE dbo.Config ADD CONSTRAINT PK_Config 
    PRIMARY KEY NONCLUSTERED (ConfigID)

Is there a way to convert a non-clustered index on a Primary Key to a clustered one? (SQL Server 2008)

If your existing PK is referenced by many other tables then you're going to spend many tedious and error-prone minutes writing the script to drop all the FK references and recreate them.

SQL Server Management Studio can do this for you. What you may not have realized is that you can only have one clustered index on a table, because the clustered index represents the physical ordering of rows; this means that you first have to bring up the clustered index and turn off clustering. Then and only then can you pull up another index and turn on clustering.

You need to do this from the table designer, then right-click and choose Indexes/Keys.... First find the existing clustered index (probably the primary key) and change Create as Clustered to No. Then go to the other index and change Create as Clustered to Yes for that. If the table is large, the operation is liable to time out while you save; you can get around this by having SSMS generate a change script (right-click on the designer after changing the indexes and you'll see the option). Then you can run this script in a query window with no timeout.

If you look at this change script, you'll see all of the work it's doing creating staging tables and switching keys around; it's a pain to write this manually. Let SSMS do it for you.

Can I make Primary Key non clustered while another index is clustered?

Yes you can. If you already have an existing table then you need to:

1. Drop the current clustered PRIMARY KEY
2. Create your CLUSTERED INDEX
3. Create a PRIMARY KEY NONCLUSTERED

For example:

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
    DROP TABLE #Test

CREATE TABLE #Test (
    ID INT, 
    TimeStamp DATETIME,
    CONSTRAINT PK_Test PRIMARY KEY (ID)) -- Clustered by default

ALTER TABLE #Test DROP PK_Test

CREATE CLUSTERED INDEX CI_Test_TimeStamp ON #Test (TimeStamp)

ALTER TABLE #Test ADD CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED (ID)

The only thing that will enforce uniqueness is the PRIMARY KEY constraint, you can still have a clustered index on repeated values, although it might raise an eyebrow for performance. See Eric's link for details.

How to convert all primary keys from clustered to nonclustered in SQL Server

The main problem is that while a primary key is both a constraint and an index, it is an index that is created via the constraint. Meaning, you create and drop via CONSTRAINT only. Once you drop the constraint, the index will be gone. So, remove SET @sql2 and the EXEC (@sql2);.

Please keep in mind that if you have any foreign keys defined that reference any of these primary keys, those will have to be dropped first and then re-created after this particular operation completes.

You can also save some query complexity by assigning both @column and @indexname variables at the same time:

SELECT @column = c.name,
       @indexname = i.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id
INNER JOIN sys.columns c ON c.object_id = t.object_id 
                       AND ic.column_id = c.column_id
WHERE i.is_primary_key = 1 
AND t.name = @table;

How to create a clustered index in SQL Server while still preserving the primary key?

You need to:

1. First remove all FK constraints to your table Player
2. Then drop the primary clustered index on Id
3. Create a new primary key with a non-clustered index on Player.Id - use this command:
   ALTER TABLE dbo.Player ADD CONSTRAINT PK_Player PRIMARY KEY NONCLUSTERED (Id);
4. Then create a clustered index on Player.Name
5. Re-establish all FK constraints from all your table, to Player

Replace a clustered index with a non-clustered one on primary key using SQL Server Management Studio UI without generating a script

Alright, with some more poking around, I found out what I had done earlier. This is the way.

1. Right-click on the primary key column when it is open in the designer and select the menu command Indexes/Keys.

2. Set Create as Clustered to the value No in the ensuing dialog.

See the pictures below.

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

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

---

Related Topics