CODEWITHSUNDEEP
sql-serverindexing
asmgx
asmgx

Reputation: 8004

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

In my table I have ID as primary key, it is just a meaningless unique code

as it is a primary key SQL Server 2017 made it clustered.

I have another column in my table called myTime this is a timestamp with non uinique non clustered index

Can I make the PK a non clustered and the index is clustered and how?

Upvotes: 0

Views: 2118

Answers (2)

EzLo
EzLo

Reputation: 14189

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.

Upvotes: 1

Zorkolot
Zorkolot

Reputation: 2027

Yes you can, by specifying the primary key be nonclustered.

ALTER TABLE TableName
ADD CONSTRAINT PK_name PRIMARY KEY NONCLUSTERED (ID);

You make another index clustered by specifying a clustered index.

CREATE CLUSTERED INDEX IX_Name   
    ON dbo.TableName (ColumnName);

Upvotes: 1

Related Questions