In SQL Server , Primary Key needs to be Clustered Index ?

After browsing thru variuous SQL Server discussion forums ,
I found that there is a misunderstanding in the SQL Server community , that stands out.

"Primary Key needs to be a clustered index" .

In most of the cases , it makes sense. But it need not be .

Here are the steps to create a Primary key that is not a clustered index.

a) Create a Heap table .
b) Create clustred index on non-key columns
c) Create a Primary Key on  the key columns

I have posted the following example in one of the forums.

Let me clarify with an example .

Step1 : Create a ( heap ) table called "emp"
============================================

1> create table emp 
2> ( 
3> empno integer not null , 
4> empfanme varchar(50) not null , 
5> emplanme varchar(50) not null 
6> ) ; 
7> go 

Step 2 : Verify the contents in DB Dictionary
=============================================

1> select name , object_id from sys.tables ; 
2> go 
name object_id 
---- --------- 
emp 341576255 

(1 rows affected) 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
NULL 0 HEAP 

(1 rows affected) 

The above result confirms that we just have a heap.


Step 3: Create Clustered Index on the non key columns
=====================================================
1> 
2> 
3> CREATE UNIQUE CLUSTERED INDEX [CIDX_EMP] ON [dbo].[emp] 
4> ( 
5> [emplanme] ASC 
6> ); 
7> go 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
CIDX_EMP 1 CLUSTERED 

(1 rows affected) 


The above result verifies that we indeed have a clustered index.


Step 4: Create a Primary Key
============================


1> alter table emp add constraint pk_emp primary key (empno); 
2> go 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
CIDX_EMP 1 CLUSTERED 
pk_emp 2 NONCLUSTERED 

(2 rows affected)

Let us re-run the sql and see the contents .
Now , we see that SQL Server created non clustered index on the Primary Key , as there can be only one clustered index in a table.


Comments welcome.