Hi Experts, Is it possible to create a primary key without Clustered index?
If yes, where exactly would you use it?

Regards
Reshma

Yes it is possible to create a primary key without clustered index. For your
better understanding find below a sample script I have attached.

--Creates a sample table with non-clustered primary key field.
Create table tblTest
(
Field1 int identity not null,
Field2 varchar(30),
Field3 int null
Constraint pk_tblTest primary key nonclustered (Field1)
)
Go

--Check the constraint type, key, name etc., (Just for cross checking)
sp_helpconstraint tblTest

1. We can have only one clustered index per table.
2. Normally all tables would have a primary key.
3. Clustered index would be created automatially if we create a PK field on
a table.

That said, based on your query pattern if you feel that another field marked
as clustered index would help the performance then this is the way to go.

Hope this helps!

Best Regards
Vadivel

http://vadivel.blogspot.com

Show quoteHide quote

Sure,

CREATE TABLE dbo.foo
(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    dt SMALLDATETIME
);
GO
CREATE CLUSTERED INDEX i_dt ON dbo.foo(dt);
GO

Hundreds of ways.  What is your question really?

A

Yes Aaron is right. We can directly say "primary key nonclustered" while
creating that field as shown in his example. But if at all you want to create
a primary key based on more than one field (composite primary key) then my
script would be of help.

i.e.,

--Creates a sample table with non-clustered primary key field.
Create table tblTest
(
Field1 int identity not null,
Field2 varchar(30),
Field3 int not null
Constraint pk_tblTest primary key nonclustered (Field1, Field3)
)
Go

Best Regards
Vadivel

http://vadivel.blogspot.com


Show quoteHide quote

Thanks for the explanation vadivel. Your second response indeed was really
helpful.

Regards
Reshma

Show quoteHide quote

convert flat file to native file format for bcp
Query Help: Counts & Sums
Disappearing data (happens rarely)
Year To Date?
trying to do some weird math in an update statement