Search

Showing posts with label Primary keys without Clustered Index. Show all posts
Showing posts with label Primary keys without Clustered Index. Show all posts

Thursday, November 17, 2011

Primary keys without Clustered Index

As we know, by default if we create a Primary Key (PK) field in a table it creates a Clustered Index automatically. Someone had asked me "Is there a way to create Primary Key (PK) field without clustered Index?”. There are three methods by which we can achieve this. 
Method 1:
Using this method we can specify it while creating the table schema itself.
Create Table Test_Table
(
Field1 int Identity not null primary key nonclustered,
Field2 nvarchar(50),
Field3 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 Test_Table
(
Field1 int Identity not null,
Field2 varchar(30),
Field3 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 constraint name 
sp_helpconstraint Test_Table 
/*
This way we could find out the constraint name. Let’s assume that our constraint name is PK_Test_Table_Field1
*/
Step 2: First drop the existing constraint
Alter table Test_Table drop constraint PK_Test_Table_Field1
Step 3: Add the new nonclustered index to that field now
Alter table Test_Table add constraint PK_parent1 primary key nonclustered (Field1)