Key and Index on Same Column?

[christopher.secord]

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?

thanks
chris

[Hugo Kornelis]

On 26 Aug 2005 12:24:19 -0700, ch************* ***@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)

[christopher.secord]

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

Thanks. That's good info.

[Gert-Jan Strik]

Chris,

Hugo already explained the technical details. Here is an other aspect of
the issue.

Both a Primary Key and a Unique constraint are logical construct that
give information about your data. They are true regardless of the
database you use.

You have designed you schema based on some world view. You have
determined that a specific column (or set of columns) is unique, and
therefore is a candidate key for the table. What you typically do, is
choose one of the candidate keys to be the Primary Key of the table, and
you specify all the other candidate keys as Unique. It would be very
confusing to specify the same set of colums as both Unique and Primary
Key, since (by definition), the Primary Key means that the key is unique
(and non-null).

If you are looking at these topics from a performance perspective, then
I would suggest you do not use the modelling concepts (Constraints), but
limit yourself to the implementation concepts (i.e. Indexes). This means
that if you remove all indexes, but keep all constraints, then the
database would still work properly (although it might be slow). In your
example, if you want to experiment with extra indexes for performance
reasons, you can simply add a unique index.

Gert-Jan
"ch************ ****@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?

thanks
chris

[christopher.secord]

Gert-Jan Strik wrote:

What you typically do, is
choose one of the candidate keys to be the Primary Key of the table, and
you specify all the other candidate keys as Unique. It would be very
confusing to specify the same set of colums as both Unique and Primary
Key, since (by definition), the Primary Key means that the key is unique
(and non-null).
I think that what threw me off was that the database even allowed me to
do both. I was trying hard to think of some reason why I would want to
do that and thought I'd go ahead and ask here. You guys have cleared
it up for me.

This means
that if you remove all indexes, but keep all constraints, then the
database would still work properly (although it might be slow).

Is there any situation where removing an index would cause the database
to not function??

chris

[Gert-Jan Strik]

"ch************ ****@gmail.com" wrote:
[snip]

This means
that if you remove all indexes, but keep all constraints, then the
database would still work properly (although it might be slow).

Is there any situation where removing an index would cause the database
to not function??

No, from a theoretical point of view, you never need to manually create
an index.

What I meant to say is if you remove the constraints (or never create
them in the first place), then you are very likely to get corruption in
your data, such as orphaned rows (missing Foreign Key), duplicate values
(missing Primary Key / Unique constraint), etc. If you have the proper
constraints in place, you can add or remove indexes without affecting
the correctness of the database.

Or course, from a practical point of view, you do need indexes. This is
because by default only Primary Keys and Unique constraints are
automatically indexed. Foreign Keys are not automatically indexed. Your
system would be unnecessarily slow without indexes (generally more I/O
needed), and SQL-Server's locking strategy would also be very limited,
which means lower concurrency (more users 'waiting' for their
transaction).

Gert-Jan

[Erland Sommarskog]

ch************* ***@gmail.com (ch************ ****@gmail.com) writes:

I think that what threw me off was that the database even allowed me to
do both. I was trying hard to think of some reason why I would want to
do that and thought I'd go ahead and ask here.

I guess the reason that SQL Server did not say anything is that no
one thought the condition was worth the extra piece of code need to
add such a check.

Just because something is possible to do with warning or error message,
does not mean that it is a meaningful or a harmless thing to do.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp