Primary keys without Clustered Index

Primary keys without Clustered Index ...

Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.

Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.

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 Empolyee
(
EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo 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 Employee
(
 EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
Constraint pk_parent primary key nonclustered (EmpID)
)
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 Employee

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

Step 2: First drop the existing constaint

Alter table Employee drop constraint PK_Employee_22568A79

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

Alter table Employee add constraint PK_EmpIdNew primary key nonclustered (EmpID )