Re: What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000?

"Steve Kass" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> Stijn Verrept wrote:
>
> >"David Portas" <[EMAIL PROTECTED]> wrote in message
> >news:[EMAIL PROTECTED]
> >
> >>Do you really allow the same Doctor, Department, etc to appear twice in
> >>
> >>
> >its
> >
> >
> >>table with different keys? If you don't declare unique natural keys then
> >>that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
> >>
> >>
> >at
> >
> >
> >>all unless the table also has a natural key - it's just a physical row
> >>identifier.
> >>
> >>
> >
> >I never said I allow them to appear twice in the column, you have Unique
> >Constraint for that.  I could use that as a natural key, but I prefer
using
> >an int or smallint.  I don't want to note Name, Firstname, ... in another
> >table as foreign key!  Also in the application I don't see me writing:
> >select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName
=
> >:FirstName) and (SN_BirthDate = :SNBirthDate).
> >
> >
> >Stijn Verrept.
> >
> >
> >
> How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
> column and putting the PRIMARY KEY NONCLUSTERED constraint on the
> multi-column primary key?

Your question demonstrates profound confusion between logical and physical.
Uniqueness is a logical constraint. Clustering is purely physical and is an
attribute of an index not of a constraint. I realize that SQL confuses the
issue by inappropriately making uniqueness a property of a physical index
structure, but I see no reason to further confuse the issue.