Answer 1)
Actually 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 tblTest ( Field1 int Identity not null primary key nonclustered, Field2 varchar(30), 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 tblTest ( 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 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) |