Primary keys without Clustered Index ...

As everyone of us know, by default if we create a Primary Key (PK) field in a table it would create Clustered Index automatically. I have been frequently asked by some of blog readers on "Is there a way to create PK fields without clustered Index?". Actually there are three methods by which we can achieve this. Let me give you the sample for both the methods below:

Method 1:

Using this method we can specify it while creating the table schema itself.

Create Table tblTest
(
Field1 int Identity not null primary key nonclustered,
Field2 varchar(30),
Field 3 int null
)
Go

Method 2:

Using this method also we could specify it while creating the table schema. It just depends on your preference.

Create Table tblTest
(
Field1 int Identity not null,
Field2 varchar(30),
Field 3 int null
Constraint pk_parent primary key nonclustered (Field1)
)
Go

Method 3:

If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method.

Step 1: Find the contraint name

sp_helpconstraint tblTest

/*
This way we could find out the constraint name. Lets assume that our constraint name is PK_tblTest_74794A92
*/

Step 2: First drop the existing constaint

Alter table tblTest drop constraint PK_tblTest_74794A92

Step 3: Add the new nonclustered index to that field now

Alter table tblTest add constraint PK_parent1 primary key nonclustered (Field1)