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

[Andy]

Hi All!

We are doing new development for SQL Server 2000 and also moving from
SQL 7.0 to SQL Server 2000.

What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?
Please, share your experience in using IDENTITY as PK .
Does SCOPE_IDENTITY makes life easier in SQL 2000?

Is there issues with DENTITY property when moving DB from one server
to another? (the same version of SQL Server)

Thank you in advance,
Andy

[Bob Badour]

"BenignVanilla" <[e-mail]> wrote in message
news:Ec********************@giganews.com...

"Bob Badour" <bb*****@golden.net> wrote in message
news:Pd********************@golden.net...
<drivel snipped>

OK Bob, I tried...I give up you win. Troll away. I am done.

With all due respect, I won nothing. You lost. And you lost only because you
will not or can not accept what was offered. I am not exactly sure what you
were trying.

[louis nguyen]

ne********@hotmail.com (Andy) wrote in message news:<ed**************************@posting.google. com>...

Hi All!

We are doing new development for SQL Server 2000 and also moving from
SQL 7.0 to SQL Server 2000.

What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?
Please, share your experience in using IDENTITY as PK .
Does SCOPE_IDENTITY makes life easier in SQL 2000?

Is there issues with DENTITY property when moving DB from one server
to another? (the same version of SQL Server)

I think this argument/war has been fought in several places. Check
out this thread: http://www.sqlteam.com/forums/topic....36&whichpage=1

My own 2 cents is natural keys is the ideal way to go. But if you
work w/ microsoft ado.net for example -- the cool databind tools
require you to have only a single col "PK". That and if you're given
an impossible deadline and half-baked requirements and you're lazy
like me...rambling....rambling... Hey my baby boy should get
discharged from the hospital today! Life is too short...
rambling...M$ and Walmart are taking over the world! Maybe in my next
job, I'll be a Walmart greeter trying to pitch web sites. :)

[Daniel Morgan]

Bob Badour wrote:

"BenignVanilla" <[e-mail]> wrote in message
news:Ec********************@giganews.com...

"Bob Badour" <bb*****@golden.net> wrote in message
news:Pd********************@golden.net...
<drivel snipped>

OK Bob, I tried...I give up you win. Troll away. I am done.

With all due respect, I won nothing. You lost. And you lost only because you
will not or can not accept what was offered. I am not exactly sure what you
were trying.

With all due respect? Where's the respect?

Look I know with Ramadan and Hanukkah and Christmas and all of that
peace on earth stuff this time of year everyone is a bit on edge. But
can we tone down the outright hostility.

We all give short answers. We all get tired of people that are lazy and
don't read the manuals. We all are irritated from time-to-time by many
things. But it seems that the number of these meangless testosterone
contests is getting a bit out of hand.

If you don't like someone, including me, please have the courtesy to
eiher deal with it off-line or ignore them. There is no requirement that
you make everyone else painfully aware of your feelings. I know it may
be cathartic but kick a trash can or break something and get over it.

Thank you.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Bob Badour]

"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1070650893.132775@yasure...

Bob Badour wrote:

"BenignVanilla" <[e-mail]> wrote in message
news:Ec********************@giganews.com...

"Bob Badour" <bb*****@golden.net> wrote in message
news:Pd********************@golden.net...
<drivel snipped>

OK Bob, I tried...I give up you win. Troll away. I am done.

With all due respect, I won nothing. You lost. And you lost only because you will not or can not accept what was offered. I am not exactly sure what you were trying.

With all due respect? Where's the respect?

The respect I feel for the individual, that I showed in the post above and
that I showed by offering my help, my knowledge and my insights. Any
disrespect you perceived in the above came from your own imagination.

Look I know with Ramadan and Hanukkah and Christmas and all of that
peace on earth stuff this time of year everyone is a bit on edge. But
can we tone down the outright hostility.
Hostility? What hostility?

We all give short answers. We all get tired of people that are lazy and
don't read the manuals. We all are irritated from time-to-time by many
things. But it seems that the number of these meangless testosterone
contests is getting a bit out of hand.
In that case, I suggest you refrain from testosterone contests. My posts
have nothing to do with testosterone. When it becomes clear to me that
someone is totally beyond help, I add him to my twit filter and move on.

If you don't like someone, including me, please have the courtesy to
eiher deal with it off-line or ignore them. There is no requirement that
you make everyone else painfully aware of your feelings. I know it may
be cathartic but kick a trash can or break something and get over it.

I am not sure what feelings you refer to, but I respectfully suggest you
project your own feelings onto what you read.

[Louis Davidson]

Boy, I can't tell whether I am enjoying this conversation, or what, but here
we go again :)

"Bob Badour" <bb*****@golden.net> wrote in message
news:ko********************@golden.net...

I never claimed that surrogate keys are a proper subset of keys--quite the
opposite. You have yet to demonstrate that surrogate keys are a proper
subset of keys. All candidate keys are surrogates, which makes candidate
keys a subset of surrogates. They are in fact a proper subset of surrogates.

How can the generic not be a superset of the specific?

How could surrogate keys not be a proper subset of keys! A key is a unique
identifying attribute (or attributes) that are used to identify an
instance/row of a table. A surrogate key is a key that is a not derived
from any other data in the database. A surrogate key is a key, a key may be
a surrogate key.

To contradict my statement, all you have to do is identify one concrete example of a useful key that is not a surrogate for anything.

DNA was a good example.

If it was such a good example, how was I able to shred the example so
easily? We have identical twins, chimeras, virus and mutation that make

DNA inappropriate to use as a candidate key. Quite simply if fails to provide
the most basic requirements of a candidate key: namely logical identity.
You are correct about identical twins. Fingerprints are a better example.
However, mutation of the key does not make it an improper key. Keys change,
over time.

A car's VIN number is (though it is a smart key
made up of many keys) not a surrogate.
A VIN is not a car. It is a surrogate invented by the automobile industry
and assigned by the manufacturer.

By your definition of surrogate, all data in the database is surrogate. In
fact, by your definition, a perfectly normalized data would be 100%
surrogate data. A VIN is not a surrogate, however, because it is based upon
other information in the database, however the data it is based upon must be
keys, and must therefore be surrogates.

While there are surrogate keys for each of the different
parts, the key itself is not a surrogate, but a description of the thing it

is representing.

In other words, you agree the VIN is not the thing. It is a surrogate for
the thing that describes the thing. You have contradicted yourself.

It is only a part of the thing in as much as the thing is represented in the
database, not in realityland. In our car table, we have:

Car
------
VIN (primary key)
Owner
Etc

This row is not the car, but the row is a thing. This thing represents the
real, and this thing contains attributes which are part of it.

No, you apparently lack intelluctual capability to discuss the concept ofa key strictly in database terms, not the real world.

I agree. I completely lack intelluctual capability--including the
intelluctual capability to ignore the real world in support of absurd
notions. I do, however, have the intellectual honesty to cede a point when
appropriate--as seldom as it is appropriate.

It is not absurd. When we model stuff in the database we represent the
essense of what makes it what it is. Each of these values becomes part of
the instance that we are modeling. So when we access a row, we are
accessing something that is representative of what we are trying to deal
with. Again, not in reality, but in the database. Kind of like how when I
related 1 + 2 = 3, 1 represents a scalar value with a value that has been
agreed upon over time. The character 1 is a surrogate for some imaginary
concept in mathetics, I suppose.

Is blue
a surrogate key for the color blue?
As I said earlier, the representation "blue" is a surrogate for the value.
Actually, it is a surrogate for several different values in different
contexts. In some contexts, it is a surrogate for a concept encompassing a
range of visible wavelengths or combinations of wavelengths at various
amplitudes perceived by the human visual processing system as a wavelength
in the previous range of visible wavelengths. In another context, it is a
surrogate for a concept encompassing a range of depressed or melancholic
emotions. In another context, it is a surrogate for a concept of sexual
content suitable for adult audiences.

You have a good understanding of the complexities of the English language.
Unfortunately you seem to have a problem with the complexities of
communication. I specifically stated the color blue. No where was porno
even realistic to include here. In computer science there are three colors,
blue, red, green. Blue is a very specific value.

Strange that you skip the obvious common usage by most of the English
speaking folks. "Having acquired worldly knowledge or refinement; lacking natural simplicity or naiveté"

Strange that you didn't notice I included "lacking natural simplicity."
Perhaps, if you also make greater effort to be observant, you will further
improve your ability to comprehend relatively simple written english.

I am sorry, I should have only stated the first part of that: "Having
acquired worldly knowledge or refinement" You are so correct. I forgot
that you were unable to communicate (meaning social intercourse between two
or more humans, not via any kind of bitstream) using common English
expressions (meaning words strung together to form a meaning) that are used
by we 21st century folks (meaning people, not Peter Paul and Mary music),
due to your dictionary-like mind (meaning the essense of what your brain is
computing, not that you mind using the dictionary) that sees (envisions, not
that you have to physically see everything, you can use imagination) every
definition in his head when a person uses a word (meaning one of these
things we are using here and not a word that is a common computing term) and
cannot differentiate between them based on typical usage.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[Louis Davidson]

He is good. I will give him that.

We all give short answers. We all get tired of people that are lazy and
don't read the manuals. We all are irritated from time-to-time by many
things. But it seems that the number of these meangless testosterone
contests is getting a bit out of hand.

In that case, I suggest you refrain from testosterone contests. My posts
have nothing to do with testosterone. When it becomes clear to me that
someone is totally beyond help, I add him to my twit filter and move on.

Huh? This thread is totally a contest. I love these kinds of threads, if
they ever get anywhere. You are interesting in some ways, but so bizarre in
some of your statements: All keys are Surrogate Keys, but Surrogates are
not a subset of Keys?

If you don't like someone, including me, please have the courtesy to
eiher deal with it off-line or ignore them. There is no requirement that
you make everyone else painfully aware of your feelings. I know it may
be cathartic but kick a trash can or break something and get over it.

I am not sure what feelings you refer to, but I respectfully suggest you
project your own feelings onto what you read.

You must be a real kick to work with. I thought I was strong-headed, but my
goodness. I cannot imagine that anyone could even vaguely interpret
anything you say.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[Bob Badour]

"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#t**************@tk2msftngp13.phx.gbl...

Boy, I can't tell whether I am enjoying this conversation, or what, but here we go again :)

"Bob Badour" <bb*****@golden.net> wrote in message
news:ko********************@golden.net...

I never claimed that surrogate keys are a proper subset of keys--quite the

opposite. You have yet to demonstrate that surrogate keys are a proper
subset of keys. All candidate keys are surrogates, which makes candidate
keys a subset of surrogates. They are in fact a proper subset of surrogates.

How can the generic not be a superset of the specific?

What makes you think it has to be a proper superset?

How could surrogate keys not be a proper subset of keys!
Keys are a superset of surrogate keys and surrogate keys are a superset of
keys. They are the same set.

Natural keys are a proper subset of surrogate keys.

A key is a unique
identifying attribute (or attributes) that are used to identify an
instance/row of a table.
I suppose your SQL background would cause you to think using those terms. A
candidate key is an irreducible set of attributes sufficing to uniquely
identify each tuple in a relation.
A surrogate key is a key that is a not derived
from any other data in the database.
Yes, that is the definition you used. A name or a driver's license number or
a serial number or any other key is then a surrogate because none of these
are derived from any other data.

However, I suggest you will find the only accepted definitions of
"surrogate" in any dictionary. It is useful to note that ISO has not deemed
it worthy of including in the standard vocabularies for IT.

http://dictionary.reference.com/search?q=surrogate

A surrogate stands for or takes the place of another. Since we cannot stuff
actual people or actual things into our databases, our candidate keys stand
for them or take their place.

A surrogate key is a key, a key may be
a surrogate key.
I disagree. All candidate keys are surrogates. You have done nothing but
assert contrary absurdities without offering any valid justification.

> To contradict my statement, all you have to do is identify one concrete > example of a useful key that is not a surrogate for anything.

DNA was a good example.

If it was such a good example, how was I able to shred the example so
easily? We have identical twins, chimeras, virus and mutation that make

DNA

inappropriate to use as a candidate key. Quite simply if fails to provide

the most basic requirements of a candidate key: namely logical identity.

You are correct about identical twins. Fingerprints are a better example.
However, mutation of the key does not make it an improper key. Keys

change, over time.
If the DNA in every cell mutated the exact same way at exactly the same
instant of time, you might have a valid point there. Unfortunately, the
probability of that happening is somewhat less than likely.

A car's VIN number is (though it is a smart key
made up of many keys) not a surrogate.

A VIN is not a car. It is a surrogate invented by the automobile industry

and assigned by the manufacturer.

By your definition of surrogate, all data in the database is surrogate.

It's not my definition, but the generally accepted definition one finds in
the dictionary.

In
fact, by your definition, a perfectly normalized data would be 100%
surrogate data. A VIN is not a surrogate, however, because it is based upon other information in the database
By the arbitrary and meaningless definition you invented to match your
absurdities, I suppose that would be the case provided the specific database
includes the data upon which each manufacturer derives the VIN. However, one
ordinarily uses the accepted term "intelligent key" to describe a candidate
key that encodes data one finds in other attributes. The VIN is no less a
surrogate for a car.

While there are surrogate keys for each of the different
parts, the key itself is not a surrogate, but a description of the thing

it

is representing.

In other words, you agree the VIN is not the thing. It is a surrogate for

the thing that describes the thing. You have contradicted yourself.

It is only a part of the thing in as much as the thing is represented in

the database, not in realityland.
Oh, I suppose for those of you who prefer to live in fantasyland that makes
an important difference. Yes, in the real world where the sane folks live, a
VIN is not a car but stands for a car.

No, you apparently lack intelluctual capability to discuss the concept ofa key strictly in database terms, not the real world.

I agree. I completely lack intelluctual capability--including the
intelluctual capability to ignore the real world in support of absurd
notions. I do, however, have the intellectual honesty to cede a point when appropriate--as seldom as it is appropriate.

It is not absurd.

Apparently, it is not absurd in your world to confuse a surrogate for the
real thing, but--for the rest of us--your notions are absurd.

When we model stuff in the database we represent the
essense of what makes it what it is.

What is the essence of a car? How do we model that essence in the database?
What exactly do you mean by essence? Intrinsic or indispensable properties?
The most important ingredient? Inherent, unchanging nature? A concentrated
extract? One that has an abundance? Something that exists?

Is blue
a surrogate key for the color blue?

As I said earlier, the representation "blue" is a surrogate for the value.

Actually, it is a surrogate for several different values in different
contexts. In some contexts, it is a surrogate for a concept encompassing a range of visible wavelengths or combinations of wavelengths at various
amplitudes perceived by the human visual processing system as a wavelength in the previous range of visible wavelengths. In another context, it is a surrogate for a concept encompassing a range of depressed or melancholic
emotions. In another context, it is a surrogate for a concept of sexual
content suitable for adult audiences.

You have a good understanding of the complexities of the English language.
Unfortunately you seem to have a problem with the complexities of
communication. I specifically stated the color blue.

You asked whether blue is a surrogate for the color blue. I pointed out that
I had already answered that question, and then I elaborated.

Sheesh! One minute I am getting criticized for being too terse and the next
I am getting criticized for clarifying points to someone who clearly has
difficulty with the language. Could you folks try to make up your minds?

Strange that you skip the obvious common usage by most of the English
speaking folks. "Having acquired worldly knowledge or refinement; lacking natural simplicity or naiveté"

Strange that you didn't notice I included "lacking natural simplicity."
Perhaps, if you also make greater effort to be observant, you will further improve your ability to comprehend relatively simple written english.

I am sorry, I should have only stated the first part of that: "Having
acquired worldly knowledge or refinement" You are so correct.

You will have to forgive me for assuming you did not intend to contradict
your previous accusation that I refuse to confine my thoughts to some
ethereal otherworldly database realm. After all, worldly does mean relating
to or devoted to the temporal (real) world.

In case you have any doubt, I am pointing and laughing right now.

[Bob Badour]

"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:uV**************@TK2MSFTNGP12.phx.gbl...

He is good. I will give him that.

We all give short answers. We all get tired of people that are lazy and don't read the manuals. We all are irritated from time-to-time by many
things. But it seems that the number of these meangless testosterone
contests is getting a bit out of hand.
In that case, I suggest you refrain from testosterone contests. My posts
have nothing to do with testosterone. When it becomes clear to me that
someone is totally beyond help, I add him to my twit filter and move on.

Huh? This thread is totally a contest.

For you, I am sure it is a contest. For me, it is not.

I love these kinds of threads, if
they ever get anywhere.
You like contests so you make threads into contests. That doesn't mean the
person on the other end sees them that way. Perhaps, if you posted something
that might present an intellectual challenge, things might change.

You are interesting in some ways, but so bizarre in
some of your statements: All keys are Surrogate Keys, but Surrogates are
not a subset of Keys?
I don't know what you find bizarre about noting that a superset is not a
subset. As I said previously, you do not appear to comprehend relatively
simple english.

If you don't like someone, including me, please have the courtesy to
eiher deal with it off-line or ignore them. There is no requirement that you make everyone else painfully aware of your feelings. I know it may
be cathartic but kick a trash can or break something and get over it.

I am not sure what feelings you refer to, but I respectfully suggest you
project your own feelings onto what you read.

You must be a real kick to work with. I thought I was strong-headed, but

my goodness. I cannot imagine that anyone could even vaguely interpret
anything you say.

Fortunately, the vast majority of people I have worked with have been quite
able to comprehend written english. Come to think of it, that is generally
true even for those for whom english was a second or third language.
Sadly, I have finally reached the conclusion that you really do lack the
ability to comprehend or to learn. Since you are totally beyond my help...
toodles!

[Louis Davidson]

"Bob Badour" <bb*****@golden.net> wrote in message
news:3a********************@golden.net...

How can the generic not be a superset of the specific?
What makes you think it has to be a proper superset?

How could surrogate keys not be a proper subset of keys!

Keys are a superset of surrogate keys and surrogate keys are a superset of
keys. They are the same set.

Then there is no need for the concept of a surrogate key, as they are the
same thing. That dictionary that seems to be quoted heavily would disagree:

http://dictionary.reference.com/sear...urrogate%20key
http://dictionary.reference.com/sear...andidate%20key

Candidate key being attributes which can be used to identify a row (they say
record, but I refuse to get into the debate on whether a row and a record
are similar or completely different concepts again.)

Surrogate Keys are unique keys that... We have covered that aleady again.

Natural keys are a proper subset of surrogate keys.
Sheesh.

A key is a unique
identifying attribute (or attributes) that are used to identify an
instance/row of a table.
I suppose your SQL background would cause you to think using those terms.

A candidate key is an irreducible set of attributes sufficing to uniquely
identify each tuple in a relation.
That is acually the most unambiguous thing that has been said here in a long
time.

A surrogate key is a key that is a not derived
from any other data in the database.
Yes, that is the definition you used. A name or a driver's license number

or a serial number or any other key is then a surrogate because none of these
are derived from any other data.

However, I suggest you will find the only accepted definitions of
"surrogate" in any dictionary. It is useful to note that ISO has not deemed it worthy of including in the standard vocabularies for IT.

http://dictionary.reference.com/search?q=surrogate

Surrogate key definition posted earlier from selfsame reference.
A surrogate stands for or takes the place of another. Since we cannot stuff actual people or actual things into our databases, our candidate keys stand for them or take their place.

A surrogate key is a key, a key may be
a surrogate key.
I disagree. All candidate keys are surrogates. You have done nothing but
assert contrary absurdities without offering any valid justification.

> > To contradict my statement, all you have to do is identify one

concrete

> > example of a useful key that is not a surrogate for anything.
>
> DNA was a good example.

If it was such a good example, how was I able to shred the example so
easily? We have identical twins, chimeras, virus and mutation that

make

DNA

inappropriate to use as a candidate key. Quite simply if fails to provide the most basic requirements of a candidate key: namely logical

identity.
You are correct about identical twins. Fingerprints are a better example. However, mutation of the key does not make it an improper key. Keys

change,

over time.

If the DNA in every cell mutated the exact same way at exactly the same
instant of time, you might have a valid point there. Unfortunately, the
probability of that happening is somewhat less than likely.

Yes, you are right here, I agree that DNA is only statistically perfect as
an identifier, not 100% perfect.

A car's VIN number is (though it is a smart key
> made up of many keys) not a surrogate.

A VIN is not a car. It is a surrogate invented by the automobile industry and assigned by the manufacturer.
By your definition of surrogate, all data in the database is surrogate.

It's not my definition, but the generally accepted definition one finds in
the dictionary.

Can I see that definition? I see why you think this, but it is
unnecessarily confusing and I have never seen any other writings to this
effect. I can agree that the entire atabase to represent person, places,
things, or ideas is a surrogate for the real thing, but that is too broad a
way to think when discussing tables and rows.

In
fact, by your definition, a perfectly normalized data would be 100%
surrogate data. A VIN is not a surrogate, however, because it is based upon

other information in the database

By the arbitrary and meaningless definition you invented to match your
absurdities, I suppose that would be the case provided the specific

database includes the data upon which each manufacturer derives the VIN. However, one ordinarily uses the accepted term "intelligent key" to describe a candidate key that encodes data one finds in other attributes. The VIN is no less a
surrogate for a car.
I agree there about the intelligent key.

> While there are surrogate keys for each of the different
> parts, the key itself is not a surrogate, but a description of the thing it
> is representing.

In other words, you agree the VIN is not the thing. It is a surrogate for the thing that describes the thing. You have contradicted yourself.

It is only a part of the thing in as much as the thing is represented in

the

database, not in realityland.

Oh, I suppose for those of you who prefer to live in fantasyland that

makes an important difference. Yes, in the real world where the sane folks live, a VIN is not a car but stands for a car.

> No, you apparently lack intelluctual capability to discuss the

concept

of

>a key strictly in database terms, not the real world.

I agree. I completely lack intelluctual capability--including the
intelluctual capability to ignore the real world in support of absurd
notions. I do, however, have the intellectual honesty to cede a point

when appropriate--as seldom as it is appropriate.

It is not absurd.

Apparently, it is not absurd in your world to confuse a surrogate for the
real thing, but--for the rest of us--your notions are absurd.

Not the real thing, the representative of the real thing as it "becomes"
real in the database. Clearly as I have agreed to, the database is a
surrogate for the real thing, but a row is a thing, and it has attributes
which serve a surrogates for the real thing.

When we model stuff in the database we represent the
essense of what makes it what it is.
What is the essence of a car? How do we model that essence in the

database? What exactly do you mean by essence? Intrinsic or indispensable properties? The most important ingredient? Inherent, unchanging nature? A concentrated
extract? One that has an abundance? Something that exists?
Every property that can be gathered about that singular thing. When we
access a given row in the car table, it should be as if we are dealing with
that very car, and only that car. So anything that we can use to describe
the car is helpful (not always possible from the users sense, but it would
be helpful)

Clearly we deal with the changing nature of object in our databases,
otherwise they would not be valueable. Of course the car may or may not be
a single table, due to the process of normalization, just as a customer in a
bank will have a table with slowly changing attributes (like name, that will
likely be in the same primary customer object) and rapidly changing
attributes, like net worth (totals of transactions.) The more information
we have the better our surrogate for the "real" thing is.

What does exist mean? Tangible? Does an idea actually exist? What defines
existance? Obviously anything that can be expressed as a noun exists in
some manner (sounds kind of new agey)

Sheesh! One minute I am getting criticized for being too terse and the next I am getting criticized for clarifying points to someone who clearly has
difficulty with the language. Could you folks try to make up your minds?
Not likely, we are humans, English speaking at that. Our language is froth
with word that have multiple meanings, inconsistencies, not to mention being
as precise as Pi. :)

You will have to forgive me for assuming you did not intend to contradict
your previous accusation that I refuse to confine my thoughts to some
ethereal otherworldly database realm. After all, worldly does mean relating to or devoted to the temporal (real) world.

In case you have any doubt, I am pointing and laughing right now.

Are you a lawyer? If not, might I suggest a change in career might make you
quite a wealthy person. If you had been the lawyer for Benedict Arnold,
this would be the USA, United States of Arnold now :)
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[Louis Davidson]

"Bob Badour" <bb*****@golden.net> wrote in message
news:qN********************@golden.net...

Huh? This thread is totally a contest.
For you, I am sure it is a contest. For me, it is not.

I love these kinds of threads, if
they ever get anywhere.

You like contests so you make threads into contests. That doesn't mean the
person on the other end sees them that way. Perhaps, if you posted

something that might present an intellectual challenge, things might change.
And this is not a contest to you? The only reason you may not consider it a
contest is that you feel me to stupid to be of any competition for your
godlike intelligence.

You are interesting in some ways, but so bizarre in
some of your statements: All keys are Surrogate Keys, but Surrogates are not a subset of Keys?
I don't know what you find bizarre about noting that a superset is not a
subset. As I said previously, you do not appear to comprehend relatively
simple english.

Agreed. Nor do I understand complex English (not sure what english is,
because English is always capitalized, see we can all catch grammatical
errors)

Sadly, I have finally reached the conclusion that you really do lack the
ability to comprehend or to learn. Since you are totally beyond my help...
toodles!

I will be sad to see this end. I was almost to turn the corner where I saw
concepts like you do, rather than everyone else who has participated in the
discussion.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[Ellen K.]

We have a couple of developers who made a database where every table
has an artificial key, and then they reload a number of the tables
every night and the keys change. I am not making this up.

On Tue, 02 Dec 2003 23:19:10 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenms.com> wrote:

"BenignVanilla" <[e-mail]> wrote in message
news:O5********************@giganews.com...

I am by no means a SQL expert, so forgive me if this seems ignorant...But
why can't the ID columm be a natural key? For example, I am working on a
project that has a vendors table. The list of vendors is used in
relationship to several other tables. We build this table with an identity
column, and a column with the vendor's name. Now when a vendor is added to
the table, they are assigned a unique ID that ties all other related data
back to this vendor, and in the case of a vendor changing their name, or a
typo, we can make updates without affecting data. Seems like a perfect use
for an identity field, and it is our primary key.

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?

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

[Ellen K.]

Come off it, Bob. Joe Celko and Ralph Kimball are hardly ignorant
about database design, and they both prefer natural keys.

On Thu, 4 Dec 2003 09:03:40 -0500, "Bob Badour" <bb*****@golden.net>
wrote:

"BenignVanilla" <[e-mail]> wrote in message
news:a7********************@giganews.com...

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:#f**************@TK2MSFTNGP11.phx.gbl...

> > Then I can only conclude you lack the ability to comprehend relatively
> > simple written english.
>
> Geez, why are you so hateful and insulting? I have absolutely nointerest > in following a conversation where every second post has a useless barb

or

ad

> hominen attack.
>
> Die, thread, die.

I concur, aside from Mr. Badour's immature, and hateful pokes, this thread
has been great. Hopefully he'll go away, or someone will mention hitler so
we can consider this thread dead.

I don't hate anyone. I provide truly helpful information to those who
actually want to learn. Making accurate observations regarding the apparent
source of the ignorami's sophistry helps those who might otherwise be duped.

In this particular case, the sophistry was little more than a
deconstructionist denial of meaning (without the actual deconstruction of
course). The man who posted it is nothing more than a self-important, puffed
up, vociferous ignoramus, and it is a service to the world to point out this
fact. Any rational, thinking person should find his post an insult to their
intelligence.

Mr. Vanilla, what was so great about this thread? Did you find it
informative? Did you find comfort in the repetition of your own
misconceptions? What was great?

[RStringfield]

> "Bob Badour" <[e-mail]> wrote in message
>A VIN is not a car. It is a surrogate invented by the automobile industry
>and assigned by the manufacturer.

It is a surrogate for a car. It would not be a surrogate key for a car table. It is not a surrogate key because vin would be an attribute in the table and it is logically connected to other data in the table, e.g., make, model, etc.

You are confusing surrogate with surrogate key, when they do not really mean the same thing. Further, you are confusing the object that is being replaced. A surrogate key is a substitute for the other data in the record. The entire record is a substitute for a car.

Surrogate key may be a poorly chosen term, but it is what it is, and that has nothing to do with the discussion.

Natural keys are neither surrogate keys or a subset of surrgate keys.