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

[Louis Davidson]

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

You are starting to get on my nerves with your claims of having
super-preciseness that no one else has. You state:
[color=blue][color=green]
> > Well, then apparently you are so good at English, and less good at being
> > precise. If natural keys and surrogate keys were in fact the same[/color][/color]
thing,[color=blue][color=green]
> > then why would we have multiple terms for these things?[/color]
>
> I don't recall saying they are the same thing. I recall saying that one is[/color]
a[color=blue]
> subset of the other. Perhaps, if you had better grasp of written english,
> you would have observed that the first time. I see nothing imprecise about
> what I said.[/color]

Then you say
[color=blue][color=green]
> > It is used as a surrogate key.[/color]
>
> All keys are surrogates.[/color]

By anyones cound, a natural key must be a key. You state that all keys are
surrogates, hence, due to our old friend the transitive property, all
natural keys are surrogates. .

[color=blue]
> A surrogate key is a unique identifying attribute that is not derived from
> any other data in the database and whose only significance is to act as an
> identifying attribute.[/color]

By any other data, it means that the value is not based on the existence of
any other data. Otherwise all normalized data (one single source) is by
your definition surrogate data.
[color=blue][color=green]
> > You name is part of what makes you you, because everyone has a name.[/color]
>
> Your statement shows a general lack of imagination. Not every infant is
> named at the moment of birth, and my name is not a part of me. It is
> external to me, and I do not change when my name changes.
>[/color]

We are talking about modeling reality in relational database, not reality in
and of itself. Every column that makes up the table becomes part of the
essense of the row/instance of the entity being modeled. Not one column in
our person table reflects any real attribute of my being, as I am a human
being (go ahead, feel free to challenge it :) and as such not electronic.
However, the row in the database models me, and has as much information to
represent me as possible/necessary.
[color=blue][color=green]
> > If it is a matter of taste, then I don't mind anyhow. I like hearing[/color]
> others[color=green]
> > opinions, and as to why my ideas are wrong if they are (and some are.)[/color]
>
> It is not a matter of taste but a matter of education. By very objective
> criteria, hiding the logical identifier from users is just plain stupid. A
> user must have access to the logical identifier to properly and to[/color]
correctly[color=blue]
> express queries.[/color]

Two reasons this is wrong. One, most users do not do any direct querying
into an OLTP database directly. Generally all queries would be built for
them by administrators (possibly the meaning of user as I was using it was
not quite clear, I should have said end users)

But second, if I express the following query:

Select fieldList
from table1
join table2
on table1.table1Id entityKey = table2.table1Id entityKey

When have I ever seen this value? I haven't. This would likely never be
seen, even by administrative users, other than for convienience of not
typing a compound key that might contain a date value, or a guid, or
whatever is needed to naturally identify the row.


[color=blue][color=green]
> > I have these discussions so I can improve my opinions that I
> > have carefully crafted over 11 years, and that I frequently give to[/color]
> others.
>
> Some people frequently give others syphyllus, but I would not congratulate
> them for the deed. I suggest you get more out of the gift than the others
> do.
>[/color]
The only think I can think to respond here is "You are a ninny." Though
that is possibly a bit sophisticated a response to such a preposterously
banal comment from someone who has such high regards for his own knowledge.
[color=blue]
> Again, I suggest your perception of anger suggests your ability to
> comprehend written english sorely lacks. I invite you to consider whether
> you project your own emotional state onto the words you read and to[/color]
consider[color=blue]
> whether this might cloud your ability to interpret the meaning of those
> words.[/color]

You are correct. You have been quite nice, and I apologise for miscontruing
your remarks as having any anger or animosity towards myself or any of the
other persons who have responded.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@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" <bbadour@golden .net> wrote in message
news:IuudnRiuHo aJyFOiRVn-ig@golden.net.. .[color=blue]
> "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> news:uOuI6hduDH A.1088@tk2msftn gp13.phx.gbl...[color=green]
> > "Bob Badour" <bbadour@golden .net> wrote in message
> > news:Oq-dnVyyJ6z0t1OiRV n-vw@golden.net.. .[color=darkred]
> > >
> > > Then I can only conclude you lack the ability to comprehend relatively
> > > simple written english.[/color]
> >
> > Well, then apparently you are so good at English, and less good at being
> > precise. If natural keys and surrogate keys were in fact the same[/color][/color]
thing,[color=blue][color=green]
> > then why would we have multiple terms for these things?[/color]
>
> I don't recall saying they are the same thing. I recall saying that one is[/color]
a[color=blue]
> subset of the other. Perhaps, if you had better grasp of written english,
> you would have observed that the first time. I see nothing imprecise about
> what I said.
>
>[color=green][color=darkred]
> > >That might be a good definition of an IDENTITY column, but it has no[/color]
> > bearing[color=darkred]
> > >on surrogate keys. By equating the identity columns with surrogate[/color][/color][/color]
keys,[color=blue][color=green]
> > you[color=darkred]
> > >only confuse yourself and potentially any similarly uneducated readers.
> > >Nothing about a surrogate key requires a DBMS to generate it.[/color]
> >
> > I did not come up with any of this terminology.[/color]
>
> That's obvious. You do not comprehend the terminology either.
>
>[color=green]
> > An identity is an
> > artificial key.[/color]
>
> It is an rdbms generated key.
>
>[color=green]
> > It is used as a surrogate key.[/color]
>
> All keys are surrogates.
>
>[/color]

[BenignVanilla]

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

You make a good point in your last post, so I'd to like respond by
requesting you show a table definition that supports your argument. My table
for sake of argument has a VENDOR_ID which is an INDENTITY field, and a
VENDOR_NAME field, let's say VARCHAR(50) for sake of argument, which
contains the vendor's name. All other tables, for instance the invoice
table, has a column for VENDOR_ID which links the invoice to a specific
vendor.

What optional design would you propose?


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

[Bob Badour]

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

If you are replying to one of my messages, I have to remark that I make good
points in all of my posts. I have no desire to waste my time on pointless
messages.

I see nothing in your request to indicate you have understood any point I
have made thus far. I certainly see no reason to think an explicit schema
definition will help you, nor do I see anything relevant in the schema
outline you have given. Either you can comprehend written english and are
willing to think, or not. Either you understand what an antecedent is and
can identify one, or not. Either you will limit your objections to what I
have actually written, or you will argue with your own prejudices.

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:orydna4-xaoUDFKiRVn-hg@giganews.com ...[color=blue]
> You make a good point in your last post, so I'd to like respond by
> requesting you show a table definition that supports your argument. My[/color]
table[color=blue]
> for sake of argument has a VENDOR_ID which is an INDENTITY field, and a
> VENDOR_NAME field, let's say VARCHAR(50) for sake of argument, which
> contains the vendor's name. All other tables, for instance the invoice
> table, has a column for VENDOR_ID which links the invoice to a specific
> vendor.
>
> What optional design would you propose?
>
>
> --
> BV.
> WebPorgmaster - www.IHeartMyPond.com
> Work at Home, Save the Environment - www.amothersdream.com
>
>[/color]

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:OoYbz7puDH A.3116@tk2msftn gp13.phx.gbl...[color=blue]
> You are starting to get on my nerves with your claims of having
> super-preciseness that no one else has. You state:
>[color=green][color=darkred]
> > > Well, then apparently you are so good at English, and less good at[/color][/color][/color]
being[color=blue][color=green][color=darkred]
> > > precise. If natural keys and surrogate keys were in fact the same[/color][/color]
> thing,[color=green][color=darkred]
> > > then why would we have multiple terms for these things?[/color]
> >
> > I don't recall saying they are the same thing. I recall saying that one[/color][/color]
is[color=blue]
> a[color=green]
> > subset of the other. Perhaps, if you had better grasp of written[/color][/color]
english,[color=blue][color=green]
> > you would have observed that the first time. I see nothing imprecise[/color][/color]
about[color=blue][color=green]
> > what I said.[/color]
>
> Then you say
>[color=green][color=darkred]
> > > It is used as a surrogate key.[/color]
> >
> > All keys are surrogates.[/color]
>
> By anyones cound, a natural key must be a key. You state that all keys[/color]
are[color=blue]
> surrogates, hence, due to our old friend the transitive property, all
> natural keys are surrogates. .[/color]

Natural keys are a subset of surrogate keys. Where have I suggested anything
different? Had I stated that natural keys and surrogate keys are the same
thing, I would have had to state that surrogate keys are a subset of natural
keys as well as vice versa. I don't recall ever writing any such thing.

[color=blue][color=green]
> > A surrogate key is a unique identifying attribute that is not derived[/color][/color]
from[color=blue][color=green]
> > any other data in the database and whose only significance is to act as[/color][/color]
an[color=blue][color=green]
> > identifying attribute.[/color]
>
> By any other data, it means that the value is not based on the existence[/color]
of[color=blue]
> any other data. Otherwise all normalized data (one single source) is by
> your definition surrogate data.[/color]

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.

[color=blue][color=green][color=darkred]
> > > You name is part of what makes you you, because everyone has a name.[/color]
> >
> > Your statement shows a general lack of imagination. Not every infant is
> > named at the moment of birth, and my name is not a part of me. It is
> > external to me, and I do not change when my name changes.
> >[/color]
>
> We are talking about modeling reality in relational database, not reality[/color]
in[color=blue]
> and of itself.[/color]

You are clutching at straws. Do you lack sufficient intellectual honesty to
cede a point when appropriate?

[color=blue][color=green][color=darkred]
> > > If it is a matter of taste, then I don't mind anyhow. I like hearing[/color]
> > others[color=darkred]
> > > opinions, and as to why my ideas are wrong if they are (and some are.)[/color]
> >
> > It is not a matter of taste but a matter of education. By very objective
> > criteria, hiding the logical identifier from users is just plain stupid.[/color][/color]
A[color=blue][color=green]
> > user must have access to the logical identifier to properly and to[/color]
> correctly[color=green]
> > express queries.[/color]
>
> Two reasons this is wrong. One, most users do not do any direct querying
> into an OLTP database directly.[/color]

All users of the dbms use the dbms. I don't care about users of other things
who do not use the dbms.


[straw man example join snipped]
[color=blue]
> When have I ever seen this value?[/color]

How do you track down an alleged data anomaly reported from the field
without identifying data?

[color=blue][color=green][color=darkred]
> > > I have these discussions so I can improve my opinions that I
> > > have carefully crafted over 11 years, and that I frequently give to[/color]
> > others.
> >
> > Some people frequently give others syphyllus, but I would not[/color][/color]
congratulate[color=blue][color=green]
> > them for the deed. I suggest you get more out of the gift than the[/color][/color]
others[color=blue][color=green]
> > do.
> >[/color]
> The only think I can think to respond here is "You are a ninny." Though
> that is possibly a bit sophisticated a response to such a preposterously
> banal comment from someone who has such high regards for his own[/color]
knowledge.

By sophisticated do you mean? Impure? Adulterated? Having used sophistry?
Lacking natural simplicity? Not genuine? Rendered worthless by admixture?
Damaged? Perverted? Debased? Corrupted? Vitiated?

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

Sophisticated has so many meanings its use is often nebulous. Regardless of
the meaning you intend, your sophistry underwhelms me.

[BenignVanilla]

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


"Bob Badour" <bbadour@golden .net> wrote in message
news:MpGdnW-DQpbZBVKiRVn-uA@golden.net.. .[color=blue]
> If you are replying to one of my messages, I have to remark that I make[/color]
good[color=blue]
> points in all of my posts. I have no desire to waste my time on pointless
> messages.
>
> I see nothing in your request to indicate you have understood any point I
> have made thus far. I certainly see no reason to think an explicit schema
> definition will help you, nor do I see anything relevant in the schema
> outline you have given. Either you can comprehend written english and are
> willing to think, or not. Either you understand what an antecedent is and
> can identify one, or not. Either you will limit your objections to what I
> have actually written, or you will argue with your own prejudices.[/color]
<snip>

I am not arguing. You misunderstand me. This thread began with me using an
example of an IDENTITY column as foreign key to other tables. You took issue
with this. In my last post, I simply stated a simplified version of my table
design, and requested you provide an alternate configuration that in your
opinion displays the correct setup, as you imply mine is flawed.

I am not arguing. I am simply asking you to show an example of your
theoretical ideas. Instead of responding with an answer, you again resort to
insults.

So I tired being polite. I tried discussing the topic with you. You refuse
to do so.

I yield. BV zero. Troll 1.


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

[Karl A. Krueger]

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

[Followups trimmed. This is not a Microsoft matter.]

In comp.databases Aaron Bertrand - MVP <aaron@trashasp faq.com> wrote:[color=blue][color=green]
>> 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[/color]
>
> An identity value that is generated by the system is not "natural".. . a
> natural key means that the key is, by nature, identifying a single row...
> not artificially because you generated some value for it. A natural key
> could be an e-mail address, or a social security number, or a license plate
> number, or a latitude and longitude -- something that is part of the data
> that also happens to uniquely identify it.[/color]

Pardon my delurk: Social Security account numbers do not form a
candidate key for people in the United States. There is no unique
mapping from SSNs onto people, nor from people onto SSNs.

Defeating the mapping from SSNs onto people is the fact that the Social
Security Administration re-uses SSNs after their holders die and
benefits are paid out.

Defeating the mapping from people onto SSNs is the fact that the SSA
has been known to assign to the same person different SSNs over their
lifetime, especially to foreign students who become immigrants.

(ISTR seeing this on someone's online SQL tutorial at one point. It
really underscores for me the idea that relations in a database really
do need to reflect relationships in the world being modeled. It also,
perhaps, shows what happens when you use _someone else's artificial key_
(which is what an SSN is!) as if it were a _natural key_. That way lies
identity theft, or at least identity confusion ....)

--
Karl A. Krueger <kkrueger@examp le.edu>
Woods Hole Oceanographic Institution
Email address is spamtrapped. s/example/whoi/
"Outlook not so good." -- Magic 8-Ball Software Reviews

[Bob Badour]

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

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:0cudnXf3T_ 4bOFKiRVn-vw@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:MpGdnW-DQpbZBVKiRVn-uA@golden.net.. .[color=green]
> > If you are replying to one of my messages, I have to remark that I make[/color]
> good[color=green]
> > points in all of my posts. I have no desire to waste my time on[/color][/color]
pointless[color=blue][color=green]
> > messages.
> >
> > I see nothing in your request to indicate you have understood any point[/color][/color]
I[color=blue][color=green]
> > have made thus far. I certainly see no reason to think an explicit[/color][/color]
schema[color=blue][color=green]
> > definition will help you, nor do I see anything relevant in the schema
> > outline you have given. Either you can comprehend written english and[/color][/color]
are[color=blue][color=green]
> > willing to think, or not. Either you understand what an antecedent is[/color][/color]
and[color=blue][color=green]
> > can identify one, or not. Either you will limit your objections to what[/color][/color]
I[color=blue][color=green]
> > have actually written, or you will argue with your own prejudices.[/color]
> <snip>
>
> I am not arguing. You misunderstand me. This thread began with me using an
> example of an IDENTITY column as foreign key to other tables. You took[/color]
issue[color=blue]
> with this.[/color]

I doubt very much that I took issue with a foreign key reference. Perhaps,
if you spent more time observing and thinking instead of assuming and
reacting, you would already know that.

[color=blue]
> In my last post, I simply stated a simplified version of my table
> design, and requested you provide an alternate configuration that in your
> opinion displays the correct setup, as you imply mine is flawed.[/color]

Where and how did I imply that? As I said in my previous post, "Either you
will limit your objections to what I have actually written, or you will
argue with your own prejudices." My statement was clear and my vocabulary
simple. Either you have the ability to comprehend simple written english, or
you do not.

You claim to have initiated this thread, which is cross-posted to three
newsgroups. You talk past what is written in the thread and refuse to
acknowledge the actual content. Yet, somehow, you claim I am the troll. Is
it not possible that you project your own character defects onto me?

[Louis Davidson]

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


"Bob Badour" <bbadour@golden .net> wrote in message
news:cpKdnaDlRo ONAFKiRVn-gg@golden.net.. .[color=blue]
> "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> news:OoYbz7puDH A.3116@tk2msftn gp13.phx.gbl...[color=green]
> > You are starting to get on my nerves with your claims of having
> > super-preciseness that no one else has. You state:
> >[color=darkred]
> > > > Well, then apparently you are so good at English, and less good at[/color][/color]
> being[color=green][color=darkred]
> > > > precise. If natural keys and surrogate keys were in fact the same[/color]
> > thing,[color=darkred]
> > > > then why would we have multiple terms for these things?
> > >
> > > I don't recall saying they are the same thing. I recall saying that[/color][/color][/color]
one[color=blue]
> is[color=green]
> > a[color=darkred]
> > > subset of the other. Perhaps, if you had better grasp of written[/color][/color]
> english,[color=green][color=darkred]
> > > you would have observed that the first time. I see nothing imprecise[/color][/color]
> about[color=green][color=darkred]
> > > what I said.[/color]
> >
> > Then you say
> >[color=darkred]
> > > > It is used as a surrogate key.
> > >
> > > All keys are surrogates.[/color]
> >
> > By anyones cound, a natural key must be a key. You state that all keys[/color]
> are[color=green]
> > surrogates, hence, due to our old friend the transitive property, all
> > natural keys are surrogates. .[/color]
>
> Natural keys are a subset of surrogate keys. Where have I suggested[/color]
anything[color=blue]
> different? Had I stated that natural keys and surrogate keys are the same
> thing, I would have had to state that surrogate keys are a subset of[/color]
natural[color=blue]
> keys as well as vice versa. I don't recall ever writing any such thing.
>[/color]

You said that all keys are surrogate keys. So KEY = SURROGATE KEY. A
NATURAL KEY is not a subset of a key, it is a key. So SURROGATE KEYS are a
subset of KEYS, and NATURAL KEYS are a subset of KEYS, and there is some
intersection of SURROGATES and NATURAL KEYS where neither is a subset of the
other.

Hence a Surrogate Key is a key, and a Natural key is a key, but a key can be
a member of the set of natural keys, or the set of surrogate keys (and since
I didn't say XOR, it can be both.)
[color=blue]
>[color=green][color=darkred]
> > > A surrogate key is a unique identifying attribute that is not derived[/color][/color]
> from[color=green][color=darkred]
> > > any other data in the database and whose only significance is to act[/color][/color][/color]
as[color=blue]
> an[color=green][color=darkred]
> > > identifying attribute.[/color]
> >
> > By any other data, it means that the value is not based on the existence[/color]
> of[color=green]
> > any other data. Otherwise all normalized data (one single source) is by
> > your definition surrogate data.[/color]
>
> 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.[/color]

DNA was a good example. A car's VIN number is (though it is a smart key
made up of many keys) not a surrogate. It is a massively compound key that
includes many real values (year produced, style, engine, and the sequence of
production etc.) 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.
[color=blue][color=green][color=darkred]
> > > > You name is part of what makes you you, because everyone has a name.
> > >
> > > Your statement shows a general lack of imagination. Not every infant[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > named at the moment of birth, and my name is not a part of me. It is
> > > external to me, and I do not change when my name changes.
> > >[/color]
> >
> > We are talking about modeling reality in relational database, not[/color][/color]
reality[color=blue]
> in[color=green]
> > and of itself.[/color]
>
> You are clutching at straws. Do you lack sufficient intellectual honesty[/color]
to[color=blue]
> cede a point when appropriate?
>[/color]

No, you apparently lack intelluctual capability to discuss the concept of a
key strictly in database terms, not the real world. By your definition,
every adjective is a surrogate key for the thing it is describing. Is blue
a surrogate key for the color blue?
[color=blue]
>[color=green][color=darkred]
> > > > If it is a matter of taste, then I don't mind anyhow. I like[/color][/color][/color]
hearing[color=blue][color=green][color=darkred]
> > > others
> > > > opinions, and as to why my ideas are wrong if they are (and some[/color][/color][/color]
are.)[color=blue][color=green][color=darkred]
> > >
> > > It is not a matter of taste but a matter of education. By very[/color][/color][/color]
objective[color=blue][color=green][color=darkred]
> > > criteria, hiding the logical identifier from users is just plain[/color][/color][/color]
stupid.[color=blue]
> A[color=green][color=darkred]
> > > user must have access to the logical identifier to properly and to[/color]
> > correctly[color=darkred]
> > > express queries.[/color]
> >
> > Two reasons this is wrong. One, most users do not do any direct[/color][/color]
querying[color=blue][color=green]
> > into an OLTP database directly.[/color]
>
> All users of the dbms use the dbms. I don't care about users of other[/color]
things[color=blue]
> who do not use the dbms.
>[/color]
[color=blue][color=green][color=darkred]
> > > > I have these discussions so I can improve my opinions that I
> > > > have carefully crafted over 11 years, and that I frequently give to
> > > others.
> > >
> > > Some people frequently give others syphyllus, but I would not[/color][/color]
> congratulate[color=green][color=darkred]
> > > them for the deed. I suggest you get more out of the gift than the[/color][/color]
> others[color=green][color=darkred]
> > > do.
> > >[/color]
> > The only think I can think to respond here is "You are a ninny." Though
> > that is possibly a bit sophisticated a response to such a preposterously
> > banal comment from someone who has such high regards for his own[/color]
> knowledge.
>
> By sophisticated do you mean? Impure? Adulterated? Having used sophistry?
> Lacking natural simplicity? Not genuine? Rendered worthless by admixture?
> Damaged? Perverted? Debased? Corrupted? Vitiated?
>
> http://dictionary.reference.com/search?q=sophisticated
>
> Sophisticated has so many meanings its use is often nebulous. Regardless[/color]
of[color=blue]
> the meaning you intend, your sophistry underwhelms me.
>[/color]

This is the most idiotic thing I have ever heard. If you do not know the
common use of the word sophistication, then you need to use that dictionary.
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é"

Meaning you argue your point like a five year old with a high IQ. Attacking
things that are not the issue to distract from a lack of refinement (and I
am not indicating that you should be purified, I mean "cultivatio n, as in
manners or taste," specifically in manners)

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@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 :)

[BenignVanilla]

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


"Bob Badour" <bbadour@golden .net> wrote in message
news:PdudnRDmer h9QVKiRVn-jA@golden.net.. .
<drivel snipped>

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


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

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:#BHNsfuuDH A.640@tk2msftng p13.phx.gbl...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:cpKdnaDlRo ONAFKiRVn-gg@golden.net.. .[color=green]
> > "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> > news:OoYbz7puDH A.3116@tk2msftn gp13.phx.gbl...[color=darkred]
> > > You are starting to get on my nerves with your claims of having
> > > super-preciseness that no one else has. You state:
> > >
> > > > > Well, then apparently you are so good at English, and less good at[/color]
> > being[color=darkred]
> > > > > precise. If natural keys and surrogate keys were in fact the same
> > > thing,
> > > > > then why would we have multiple terms for these things?
> > > >
> > > > I don't recall saying they are the same thing. I recall saying that[/color][/color]
> one[color=green]
> > is[color=darkred]
> > > a
> > > > subset of the other. Perhaps, if you had better grasp of written[/color]
> > english,[color=darkred]
> > > > you would have observed that the first time. I see nothing imprecise[/color]
> > about[color=darkred]
> > > > what I said.
> > >
> > > Then you say
> > >
> > > > > It is used as a surrogate key.
> > > >
> > > > All keys are surrogates.
> > >
> > > By anyones cound, a natural key must be a key. You state that all[/color][/color][/color]
keys[color=blue][color=green]
> > are[color=darkred]
> > > surrogates, hence, due to our old friend the transitive property, all
> > > natural keys are surrogates. .[/color]
> >
> > Natural keys are a subset of surrogate keys. Where have I suggested[/color]
> anything[color=green]
> > different? Had I stated that natural keys and surrogate keys are the[/color][/color]
same[color=blue][color=green]
> > thing, I would have had to state that surrogate keys are a subset of[/color]
> natural[color=green]
> > keys as well as vice versa. I don't recall ever writing any such thing.
> >[/color]
>
> You said that all keys are surrogate keys. So KEY = SURROGATE KEY. A
> NATURAL KEY is not a subset of a key, it is a key. So SURROGATE KEYS are a
> subset of KEYS, and NATURAL KEYS are a subset of KEYS, and there is some
> intersection of SURROGATES and NATURAL KEYS where neither is a subset of[/color]
the[color=blue]
> other.[/color]

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.

[color=blue][color=green][color=darkred]
> > > > A surrogate key is a unique identifying attribute that is not[/color][/color][/color]
derived[color=blue][color=green]
> > from[color=darkred]
> > > > any other data in the database and whose only significance is to act[/color][/color]
> as[color=green]
> > an[color=darkred]
> > > > identifying attribute.
> > >
> > > By any other data, it means that the value is not based on the[/color][/color][/color]
existence[color=blue][color=green]
> > of[color=darkred]
> > > any other data. Otherwise all normalized data (one single source) is[/color][/color][/color]
by[color=blue][color=green][color=darkred]
> > > your definition surrogate data.[/color]
> >
> > 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.[/color]
>
> DNA was a good example.[/color]

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.

[color=blue]
> A car's VIN number is (though it is a smart key
> made up of many keys) not a surrogate.[/color]

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

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

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.

[color=blue][color=green][color=darkred]
> > > > > You name is part of what makes you you, because everyone has a[/color][/color][/color]
name.[color=blue][color=green][color=darkred]
> > > >
> > > > Your statement shows a general lack of imagination. Not every infant[/color][/color]
> is[color=green][color=darkred]
> > > > named at the moment of birth, and my name is not a part of me. It is
> > > > external to me, and I do not change when my name changes.
> > > >
> > >
> > > We are talking about modeling reality in relational database, not[/color][/color]
> reality[color=green]
> > in[color=darkred]
> > > and of itself.[/color]
> >
> > You are clutching at straws. Do you lack sufficient intellectual honesty[/color]
> to[color=green]
> > cede a point when appropriate?
> >[/color]
>
> No, you apparently lack intelluctual capability to discuss the concept of[/color]
a[color=blue]
> key strictly in database terms, not the real world.[/color]

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.

[color=blue]
> Is blue
> a surrogate key for the color blue?[/color]

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.

[color=blue][color=green][color=darkred]
> > > > > If it is a matter of taste, then I don't mind anyhow. I like[/color][/color]
> hearing[color=green][color=darkred]
> > > > others
> > > > > opinions, and as to why my ideas are wrong if they are (and some[/color][/color]
> are.)[color=green][color=darkred]
> > > >
> > > > It is not a matter of taste but a matter of education. By very[/color][/color]
> objective[color=green][color=darkred]
> > > > criteria, hiding the logical identifier from users is just plain[/color][/color]
> stupid.[color=green]
> > A[color=darkred]
> > > > user must have access to the logical identifier to properly and to
> > > correctly
> > > > express queries.
> > >
> > > Two reasons this is wrong. One, most users do not do any direct[/color][/color]
> querying[color=green][color=darkred]
> > > into an OLTP database directly.[/color]
> >
> > All users of the dbms use the dbms. I don't care about users of other[/color]
> things[color=green]
> > who do not use the dbms.
> >[/color]
>[color=green][color=darkred]
> > > > > I have these discussions so I can improve my opinions that I
> > > > > have carefully crafted over 11 years, and that I frequently give[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > others.
> > > >
> > > > Some people frequently give others syphyllus, but I would not[/color]
> > congratulate[color=darkred]
> > > > them for the deed. I suggest you get more out of the gift than the[/color]
> > others[color=darkred]
> > > > do.
> > > >
> > > The only think I can think to respond here is "You are a ninny."[/color][/color][/color]
Though[color=blue][color=green][color=darkred]
> > > that is possibly a bit sophisticated a response to such a[/color][/color][/color]
preposterously[color=blue][color=green][color=darkred]
> > > banal comment from someone who has such high regards for his own[/color]
> > knowledge.
> >
> > By sophisticated do you mean? Impure? Adulterated? Having used[/color][/color]
sophistry?[color=blue][color=green]
> > Lacking natural simplicity? Not genuine? Rendered worthless by[/color][/color]
admixture?[color=blue][color=green]
> > Damaged? Perverted? Debased? Corrupted? Vitiated?
> >
> > http://dictionary.reference.com/search?q=sophisticated
> >
> > Sophisticated has so many meanings its use is often nebulous. Regardless[/color]
> of[color=green]
> > the meaning you intend, your sophistry underwhelms me.
> >[/color]
>
> This is the most idiotic thing I have ever heard. If you do not know the
> common use of the word sophistication, then you need to use that[/color]
dictionary.

The whole point is I do know the meanings of sophistication: unnaturalness,
impurity, complexity and sophistry. Which of them did you mean?

I suggest, if you make an effort to understand the words you see and use,
you will improve your ability to comprehend relatively simple english.
Otherwise, you simply refuse to communicate.

[color=blue]
> 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é"[/color]

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.

[Bob Badour]

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

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Ec-dnVzXatUZLU2iRV n-vg@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:PdudnRDmer h9QVKiRVn-jA@golden.net.. .
> <drivel snipped>
>
> OK Bob, I tried...I give up you win. Troll away. I am done.[/color]

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]

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

net__space@hotm ail.com (Andy) wrote in message news:<edb90340. 0311301240.11f2 d584@posting.go ogle.com>...[color=blue]
> 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)[/color]

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]

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

Bob Badour wrote:[color=blue]
> "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> news:Ec-dnVzXatUZLU2iRV n-vg@giganews.com ...
>[color=green]
>>"Bob Badour" <bbadour@golden .net> wrote in message
>>news:PdudnRDm erh9QVKiRVn-jA@golden.net.. .
>><drivel snipped>
>>
>>OK Bob, I tried...I give up you win. Troll away. I am done.[/color]
>
>
> 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.[/color]

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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Bob Badour]

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

"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1070650893 .132775@yasure. ..[color=blue]
> Bob Badour wrote:[color=green]
> > "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> > news:Ec-dnVzXatUZLU2iRV n-vg@giganews.com ...
> >[color=darkred]
> >>"Bob Badour" <bbadour@golden .net> wrote in message
> >>news:PdudnRDm erh9QVKiRVn-jA@golden.net.. .
> >><drivel snipped>
> >>
> >>OK Bob, I tried...I give up you win. Troll away. I am done.[/color]
> >
> >
> > With all due respect, I won nothing. You lost. And you lost only because[/color][/color]
you[color=blue][color=green]
> > will not or can not accept what was offered. I am not exactly sure what[/color][/color]
you[color=blue][color=green]
> > were trying.[/color]
>
> With all due respect? Where's the respect?[/color]

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.

[color=blue]
> 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.[/color]

Hostility? What hostility?

[color=blue]
> 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.[/color]

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.

[color=blue]
> 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.[/color]

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

[Louis Davidson]

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

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

"Bob Badour" <bbadour@golden .net> wrote in message
news:komdnXvws-uhVE2iRVn-vg@golden.net.. .[color=blue]
>
> 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[/color]
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.
[color=blue][color=green][color=darkred]
> > >
> > > To contradict my statement, all you have to do is identify one[/color][/color][/color]
concrete[color=blue][color=green][color=darkred]
> > > example of a useful key that is not a surrogate for anything.[/color]
> >
> > DNA was a good example.[/color]
>
> 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[/color]
DNA[color=blue]
> inappropriate to use as a candidate key. Quite simply if fails to provide
> the most basic requirements of a candidate key: namely logical identity.[/color]

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.
[color=blue]
>
>[color=green]
> > A car's VIN number is (though it is a smart key
> > made up of many keys) not a surrogate.[/color]
>
> A VIN is not a car. It is a surrogate invented by the automobile industry
> and assigned by the manufacturer.[/color]

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.
[color=blue]
>
>[color=green]
> > While there are surrogate keys for each of the different
> > parts, the key itself is not a surrogate, but a description of the thing[/color]
> it[color=green]
> > is representing.[/color]
>
> 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.
>[/color]

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.
[color=blue][color=green]
> >
> > No, you apparently lack intelluctual capability to discuss the concept[/color][/color]
of[color=blue][color=green]
> >a key strictly in database terms, not the real world.[/color]
>
> 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.
>[/color]
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.
[color=blue]
>[color=green]
> > Is blue
> > a surrogate key for the color blue?[/color]
>
> 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.
>[/color]
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.
[color=blue]
>[color=green]
> > Strange that you skip the obvious common usage by most of the English
> > speaking folks. "Having acquired worldly knowledge or refinement;[/color][/color]
lacking[color=blue][color=green]
> > natural simplicity or naiveté"[/color]
>
> 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.
>[/color]

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 (drsql@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 :)