Sql Server: How to create PRIMARY KEY without CLUSTERED Index
- Posted by Sqltimes
- On November 22, 2014
- 0 Comments
Quick one today:
On a table, we could create Clustered and NonClustered indexes. Each table can only have 1 clustered index, but multiple nonclustered index.
When a primary key is created on a column, it by default becomes Clustered index. But sometimes, we run into a situation where we need to separate PRIMARY KEY and Clustered index. It sounds counter intuitive, but this distinction is important and in some cases necessary to achieve better performance.
As an example, we use Product table.
To accomplish this, we need the following steps.
Step 1: Drop the Primary Key constraint. This also drops the Clustered index on that column. Recreate Primary Key constraint with Nonclustered index
Step 2: Create Clustered index on a different column
Step 1: Drop PK Constraint and recreate it without Clustered index
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
---- First DROP the Primary Key constraint and then recreate it with NonClustered Index--ALTER TABLE dbo.ProductDROP CONSTRAINT PK_Product_IDGO---- Notice the NONCLUSTERED clause--ALTER TABLE dbo.ProductADD CONSTRAINT PK_Product_ID PRIMARY KEY NONCLUSTERED (Id ASC)ON [nCI_01]GO |
Step 2: Recreate CLUSTERED INDEX without Primary Key
|
1
2
3
4
5
6
7
8
9
10
11
|
---- Create Clustered index on a different column--CREATE CLUSTERED INDEX CI_Product_Kiosk_ID ON dbo.Product (Kiosk_ID ASC)WITH ( SORT_IN_TEMPDB = ON , MAXDOP = 2 , FILLFACTOR = 80) ON [CI_01]GO |
Hope this helps,
0 Comments