Primary key without clustered index

Hi Experts, Is it possible to create a primary key without Clustered index?
If yes, where exactly would you use it?
Regards
Reshma> Hi Experts, Is it possible to create a primary key without Clustered
> index?
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

> If yes, where exactly would you use it?
Hundreds of ways. What is your question really?
A|||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
"Reshma" wrote:

> Hi Experts, Is it possible to create a primary key without Clustered index
?
> If yes, where exactly would you use it?
> Regards
> Reshma|||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 creat
e
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
"Aaron Bertrand [SQL Server MVP]" wrote:

> 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
>
>|||Thanks for the explanation vadivel. Your second response indeed was really
helpful.
Regards
Reshma
"Reshma" wrote:

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