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

"Greg D. Moore (Strider)" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> "Trey Walpole" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > > And what happens if someone does a DBCC checkident ('FOO', RESEED)?
> > >
> > > Or you have to copy it into a new table and accidently set that table
up
> > > with an IDENTITY column and now all your rows get new IDs?
> >
> > Fire the DBA. If they've allowed unskilled people such access or do
> anything
> > accidentally, no telling what other problems they'll cause ;)
>
> Wow.  Can I get a job where you work where folks never make mistakes?
>
> Seriously, a DBCC checkident can be necessary in some recovery scenarios.
>
> Copying a table over into another one is often necessary in general
> maintenance, schema changes, etc.  It's pretty easy to forget to do it
> right.
>

I was, of course, being facetious -- well, mostly. :*) Mistakes do happen,
but in most situations in db management, like these mentioned, they are
completely avoidable.

The DBA should be the one doing the DBCC CHECKIDENT - not just "someone".
And if it is an approveed someone else, the DBA better know about it.

Copying a table's data over is often necessary, but you do need to be very
careful and know all the things that might be affected. Again, since this is
a DBA responsibility, he'd better know how to do it right and have some test
db to work with.
[And if that scenario happens, it's pretty easy to fix, although it does
mean moving data all over again.]

> Admittedly, they are pretty contrived examples, but the point is, the
value
> of the identity relies on some arbitrary state of the DB at the point in
> time it is created.
>
> Now, in some cases that just might not matter, but in many cases it can be
> an important factor.
>

Also very true. But it is the DBA that has [or should have] the control over
any modifications that affect identity values, whereas any user has the
ability to change natural keys [which was the point I was making :)]