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

Bob Badour wrote:

> "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.
>  
Then don't.

Or else be honest.  If you see no reason to confuse the issue further, 
why quote me out of context by deleting my next paragraph, which made it 
clear I was joking?  [In case it's not clear, this is a rhetorical 
question.  I don't really want an answer from you.]

SK




>