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
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)
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)

No comments:

Post a Comment