On 26 Aug 2005 12:24:19 -0700, christopher.secord@gmail.com wrote:

>Is there any advantage to doing this:
>
>ALTER TABLE testtable ADD
> CONSTRAINT PK_sysUser
> PRIMARY KEY NONCLUSTERED (UserID)
> WITH FILLFACTOR = 100,
>
> CONSTRAINT IX_sysUser
> UNIQUE NONCLUSTERED (UserID)
> WITH FILLFACTOR = 100
>GO
>
>over just having the primary key? Does having both an index and a
>primary key add anything?

Hi Chris,

No.

When you declare a PRIMARY KEY constraint, SQL Server will immediately
create an index to support it. Default is clustered, but in this case,
you override the default and get a non-clustered index.

When you declare a UNIQUE constraint, SQL Server will immediately create
an index to support it. Default is nonclustered; in this case you're
also asking for non-clustered, so non-clustered is what you'll get.

The end result: two exactly identical nonclustered indexes, double
overhead on data modification, and one of those indexes will never be
used.

However, if you define the primary key with the default clustered
option, there are some circumstances where an extra nonclustered index
on the same column might help. If the table is wide, but some queries
use only the primary key value, a scan of the nonclustered index will be
faster than a scan of the clustered index. I'd not use the UNIQUE
constraint to declare such an index, thoug, but use a CREATE INDEX
statement to stress that this is just a supporting index instead of
another constraint.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)