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

[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:QaadnQDRRb qSplKiRVn-vA@golden.net.. .
<snip>[color=blue]
> With all due respect, the user of the dbms does care. Think about it.
>
>[/color]

Please explain. I do not understand your point. For our application, the
user works an invoice, they want to pick the Vendor by name. We need to
associate that invoice to a vendor, we use the internal ID. Same goes for
contracts, service line items, etc. Using the internal ID allows us to
connect our records, the user doesn't care, nor does s/he need to know about
it. With this design, when the user goes in and changes a vendor name, which
they can and do from time to time, the ID keeps the records linked.


--
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?

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:QqadnYnBmI N031KiRVn-jg@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:QaadnQDRRb qSplKiRVn-vA@golden.net.. .
> <snip>[color=green]
> > With all due respect, the user of the dbms does care. Think about it.[/color]
>
> Please explain. I do not understand your point.[/color]

That's because you have not thought about it yet. Either that or you really
do lack the ability to comprehend simple written english.

[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:J-SdnW0d8J9eo1KiR Vn-uw@golden.net.. .
<snip>[color=blue]
> I don't hate anyone. I provide truly helpful information to those who
> actually want to learn. Making accurate observations regarding the[/color]
apparent[color=blue]
> source of the ignorami's sophistry helps those who might otherwise be[/color]
duped.[color=blue]
>
> In this particular case, the sophistry was little more than a
> deconstructioni st denial of meaning (without the actual deconstruction of
> course). The man who posted it is nothing more than a self-important,[/color]
puffed[color=blue]
> up, vociferous ignoramus, and it is a service to the world to point out[/color]
this[color=blue]
> fact. Any rational, thinking person should find his post an insult to[/color]
their[color=blue]
> intelligence.[/color]

That is very useful. If you didn't have an apparent knowledge of DBMS's I'd
believe you were a troll.
[color=blue]
> 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?[/color]

I have found the active discussion of pro's/con's to be informative. Clearly
there are some subjective aspects to this topic. If it were clearly an
objective topic, the thread would not have survived this long, and we would
long ago have seen a link to documentation explicitly stating the CORRECT
method. I believe that there is not one clearcut correct way, so the thread
is therefore informative and interesting to me.


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

[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:DJSdne8Dxc rXolKiRVn-sQ@golden.net.. .[color=blue]
> "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> news:p8ednRh9HI QppFKi4p2dnA@gi ganews.com...[color=green]
> >
> > "Bob Badour" <bbadour@golden .net> wrote in message
> > news:IuudnRiuHo aJyFOiRVn-ig@golden.net.. .
> > <snip>[color=darkred]
> > > > It is not a natural key.
> > >
> > > While the key remains unfamiliar, this is true. However, as soon as it
> > > becomes familiar, this ceases to be the case. I was assigned an[/color][/color]
> arbitrary[color=green][color=darkred]
> > > nine digit university identification number two degades ago. I can[/color][/color][/color]
still[color=blue][color=green][color=darkred]
> > > recite this number easily having used it on literally hundreds if not
> > > thousands of pieces of correspondence. That sounds like a natural[/color][/color][/color]
enough[color=blue][color=green]
> > key[color=darkred]
> > > to me.[/color]
> > <snip>
> >
> > So, are you saying an arbritrary number assigned to you by your collegis[/color][/color]
a[color=blue][color=green]
> > natural key because it has become famaliar to you, but an IDENTITY field
> > can't be?[/color]
>
> I suggest you apparently lack the ability to comprehend relatively simple
> written english as well. Could you point out anything I wrote that would
> lead you to conclude I ever said anything so ridiculous?[/color]

I did as you ask in my original post, where I quoted your post. It was my
interpretation of that paragraph that led me to ask the question. I was not
making a statement, I was asking a question. You seemed to say, in the above
paragraph, that the number assigned to you by your college "sounds like a
natural enough key", so I was inquiring as to why this arbitrary number is
any more natural then an IDENTITY field.


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

[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:ruidnT2SRc KC2VKiRVn-sA@golden.net.. .[color=blue]
> "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> news:QqadnYnBmI N031KiRVn-jg@giganews.com ...[color=green]
> >
> > "Bob Badour" <bbadour@golden .net> wrote in message
> > news:QaadnQDRRb qSplKiRVn-vA@golden.net.. .
> > <snip>[color=darkred]
> > > With all due respect, the user of the dbms does care. Think about it.[/color]
> >
> > Please explain. I do not understand your point.[/color]
>
> That's because you have not thought about it yet. Either that or you[/color]
really[color=blue]
> do lack the ability to comprehend simple written english.[/color]

With each post, I believe you to be more of a troll. We are discussing a
specific topic here. You have taken issue with one of my questions. If you
will simply make insulting remarks and continue to refrain from answering
the questions with what you believe to be the correct answer, you do nothing
but reduce your credibility.

I understand the design I am discussing, and believe it to be a good one. If
you believe it not to be, explain why.


--
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?


"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:6fudnQlhto W22VKiRVn-vg@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:J-SdnW0d8J9eo1KiR Vn-uw@golden.net.. .
> <snip>[color=green]
> > I don't hate anyone. I provide truly helpful information to those who
> > actually want to learn. Making accurate observations regarding the[/color]
> apparent[color=green]
> > source of the ignorami's sophistry helps those who might otherwise be[/color]
> duped.[color=green]
> >
> > In this particular case, the sophistry was little more than a
> > deconstructioni st denial of meaning (without the actual deconstruction[/color][/color]
of[color=blue][color=green]
> > course). The man who posted it is nothing more than a self-important,[/color]
> puffed[color=green]
> > up, vociferous ignoramus, and it is a service to the world to point out[/color]
> this[color=green]
> > fact. Any rational, thinking person should find his post an insult to[/color]
> their[color=green]
> > intelligence.[/color]
>
> That is very useful. If you didn't have an apparent knowledge of DBMS's[/color]
I'd[color=blue]
> believe you were a troll.
>[color=green]
> > 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?[/color]
>
> I have found the active discussion of pro's/con's to be informative.[/color]
Clearly[color=blue]
> there are some subjective aspects to this topic. If it were clearly an
> objective topic, the thread would not have survived this long, and we[/color]
would[color=blue]
> long ago have seen a link to documentation explicitly stating the CORRECT
> method. I believe that there is not one clearcut correct way, so the[/color]
thread[color=blue]
> is therefore informative and interesting to me.[/color]

"familiarit y, irreducibility, simplicity, stability"

http://www.dbdebunk.com/page/page/622344.htm

I don't use reducible keys so I generally simplify the above to familiarity,
simplicity and stability, which I have stated several times in this thread.
Nobody in this thread has contributed anything beyond those criteria;
although, most posters have danced around the issue without actually stating
what the criteria are.

Because most of the contributors to this thread are profoundly ignorant of
fundamentals, they lack the ability to state anything succinctly, and
instead they veer off onto wild tangents where they insist others accept
their ignorant misconceived definitions of simple terms.

Did you really find the thread informative? If so, how were you informed by
the thread? What knowledge did you acquire? Or are you really saying you are
comforted by the idea that you can just accept your subjective biases
without further thought?

[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:6fudnQhhto W22VKiRVn-vg@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:DJSdne8Dxc rXolKiRVn-sQ@golden.net.. .[color=green]
> > "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> > news:p8ednRh9HI QppFKi4p2dnA@gi ganews.com...[color=darkred]
> > >
> > > "Bob Badour" <bbadour@golden .net> wrote in message
> > > news:IuudnRiuHo aJyFOiRVn-ig@golden.net.. .
> > > <snip>
> > > > > It is not a natural key.
> > > >
> > > > While the key remains unfamiliar, this is true. However, as soon as[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > > becomes familiar, this ceases to be the case. I was assigned an[/color]
> > arbitrary[color=darkred]
> > > > nine digit university identification number two degades ago. I can[/color][/color]
> still[color=green][color=darkred]
> > > > recite this number easily having used it on literally hundreds if[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > > thousands of pieces of correspondence. That sounds like a natural[/color][/color]
> enough[color=green][color=darkred]
> > > key
> > > > to me.
> > > <snip>
> > >
> > > So, are you saying an arbritrary number assigned to you by your[/color][/color][/color]
collegis[color=blue]
> a[color=green][color=darkred]
> > > natural key because it has become famaliar to you, but an IDENTITY[/color][/color][/color]
field[color=blue][color=green][color=darkred]
> > > can't be?[/color]
> >
> > I suggest you apparently lack the ability to comprehend relatively[/color][/color]
simple[color=blue][color=green]
> > written english as well. Could you point out anything I wrote that would
> > lead you to conclude I ever said anything so ridiculous?[/color]
>
> I did as you ask in my original post, where I quoted your post. It was my
> interpretation of that paragraph that led me to ask the question. I was[/color]
not[color=blue]
> making a statement, I was asking a question. You seemed to say, in the[/color]
above[color=blue]
> paragraph, that the number assigned to you by your college "sounds like a
> natural enough key", so I was inquiring as to why this arbitrary number is
> any more natural then an IDENTITY field.[/color]

I said nothing to indicate that it is any more natural than an identity
field. I suggest your incapacity with respect to written english amounts to
a failure to apply the closed world assumption.

[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:c5SdnbjsVf xy21KiRVn-ig@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:ruidnT2SRc KC2VKiRVn-sA@golden.net.. .[color=green]
> > "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> > news:QqadnYnBmI N031KiRVn-jg@giganews.com ...[color=darkred]
> > >
> > > "Bob Badour" <bbadour@golden .net> wrote in message
> > > news:QaadnQDRRb qSplKiRVn-vA@golden.net.. .
> > > <snip>
> > > > With all due respect, the user of the dbms does care. Think about[/color][/color][/color]
it.[color=blue][color=green][color=darkred]
> > >
> > > Please explain. I do not understand your point.[/color]
> >
> > That's because you have not thought about it yet. Either that or you[/color]
> really[color=green]
> > do lack the ability to comprehend simple written english.[/color]
>
> With each post, I believe you to be more of a troll. We are discussing a
> specific topic here. You have taken issue with one of my questions. If you
> will simply make insulting remarks and continue to refrain from answering
> the questions with what you believe to be the correct answer, you do[/color]
nothing[color=blue]
> but reduce your credibility.
>
> I understand the design I am discussing, and believe it to be a good one.[/color]
If[color=blue]
> you believe it not to be, explain why.[/color]

When the user of the dbms must track down an alleged anomaly in the data,
how does the user proceed?

[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:DdidnUPv4q 8-1FKiRVn-jg@golden.net.. .
<snip>[color=blue]
> Because most of the contributors to this thread are profoundly ignorant of
> fundamentals, they lack the ability to state anything succinctly, and
> instead they veer off onto wild tangents where they insist others accept
> their ignorant misconceived definitions of simple terms.[/color]
<snip>

I guess we'll have to agree to disagree, as I do not see your posts
contributing to the thread, and you don't see me doing that as well.

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

[Stijn Verrept]

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:c5SdnbjsVf xy21KiRVn-ig@giganews.com ...[color=blue][color=green]
> > That's because you have not thought about it yet. Either that or you[/color]
> really[color=green]
> > do lack the ability to comprehend simple written english.[/color]
>
> With each post, I believe you to be more of a troll.[/color]

Let me free you of any doubts: if it looks like a troll, smells like a troll
and sounds like a troll: it's a troll!!!
And a boring one too because I hear nothing else but: "lack the ability to
comprehend simple written english.". And I can assure you, I comprehend
simple written English as well as Dutch, French and German :-P

[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:-Pqdnan4lMVGzVKi RVn-tw@giganews.com ...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:DdidnUPv4q 8-1FKiRVn-jg@golden.net.. .
> <snip>[color=green]
> > Because most of the contributors to this thread are profoundly ignorant[/color][/color]
of[color=blue][color=green]
> > fundamentals, they lack the ability to state anything succinctly, and
> > instead they veer off onto wild tangents where they insist others accept
> > their ignorant misconceived definitions of simple terms.[/color]
> <snip>
>
> I guess we'll have to agree to disagree, as I do not see your posts
> contributing to the thread, and you don't see me doing that as well.[/color]

I doubt we disagree on the data management issues, and I am quite content to
agree to disagree on the rest.

[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:vtKdnfA4UO al1lKiRVn-hA@golden.net.. .
<snip>[color=blue]
> When the user of the dbms must track down an alleged anomaly in the data,
> how does the user proceed?[/color]

That is a valid point. My answer would be that we have two levels of users.
The "app users" that never see the ID, would not be tracking such an
anomaly. So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent, we have a
level of DB users that could analyze the tables directly to solve the issue
and hopefully provide a solution to prevent further anomolies.


--
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?

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Ft2dnYvlCp oEylKi4p2dnA@gi ganews.com...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:vtKdnfA4UO al1lKiRVn-hA@golden.net.. .
> <snip>[color=green]
> > When the user of the dbms must track down an alleged anomaly in the[/color][/color]
data,[color=blue][color=green]
> > how does the user proceed?[/color]
>
> That is a valid point. My answer would be that we have two levels of[/color]
users.[color=blue]
> The "app users" that never see the ID, would not be tracking such an
> anomaly.[/color]

In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an anomaly
in their data and use the identifier they see to track it down.

The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.

[color=blue]
> So that is not an issue for us. If such an anomoly were to arise,
> which hopefully the design and proper management would prevent[/color]

No amount of design and proper management can eliminate human error or
malice.

[color=blue]
>, we have a
> level of DB users that could analyze the tables directly to solve the[/color]
issue[color=blue]
> and hopefully provide a solution to prevent further anomolies.[/color]

These users see a different view of the data or perhaps a proper superset of
the "app users" view of the data. They, of course, can see the identifiers
they use.

[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:7qCdnaq8dt 5G_VKiRVn-hQ@golden.net.. .[color=blue]
> "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> news:Ft2dnYvlCp oEylKi4p2dnA@gi ganews.com...[color=green]
> >
> > "Bob Badour" <bbadour@golden .net> wrote in message
> > news:vtKdnfA4UO al1lKiRVn-hA@golden.net.. .
> > <snip>[color=darkred]
> > > When the user of the dbms must track down an alleged anomaly in the[/color][/color]
> data,[color=green][color=darkred]
> > > how does the user proceed?[/color]
> >
> > That is a valid point. My answer would be that we have two levels of[/color]
> users.[color=green]
> > The "app users" that never see the ID, would not be tracking such an
> > anomaly.[/color]
>
> In other words, the "app users" use a view of the data with a different
> identifier. They see the identifier they use. They might perceive an[/color]
anomaly[color=blue]
> in their data and use the identifier they see to track it down.[/color]

True.
[color=blue]
> The relational model has views and snapshots, ie. named derived relation
> variables, to address this very issue. It is revealing to consider the
> candidate keys of these derived relation variables.
>
>[color=green]
> > So that is not an issue for us. If such an anomoly were to arise,
> > which hopefully the design and proper management would prevent[/color]
>
> No amount of design and proper management can eliminate human error or
> malice.[/color]

True, which is why I like the IDENTITY fields in this case. By allowing the
DB to assign a unique key to the records in this particular table, I reduce
the human error component by one by not relying on a user to properly link
this record to others, or improperly break that link with a typo.
[color=blue]
>[color=green]
> >, we have a
> > level of DB users that could analyze the tables directly to solve the[/color]
> issue[color=green]
> > and hopefully provide a solution to prevent further anomolies.[/color]
>
> These users see a different view of the data or perhaps a proper superset[/color]
of[color=blue]
> the "app users" view of the data. They, of course, can see the identifiers
> they use.
>[/color]

True.


--
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?

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Wf-dnceHpOLI8FKi4p 2dnA@giganews.c om...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:7qCdnaq8dt 5G_VKiRVn-hQ@golden.net.. .[color=green]
> > "BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
> > news:Ft2dnYvlCp oEylKi4p2dnA@gi ganews.com...[color=darkred]
> > >
> > > "Bob Badour" <bbadour@golden .net> wrote in message
> > > news:vtKdnfA4UO al1lKiRVn-hA@golden.net.. .
> > > <snip>
> > > > When the user of the dbms must track down an alleged anomaly in the[/color]
> > data,[color=darkred]
> > > > how does the user proceed?
> > >
> > > That is a valid point. My answer would be that we have two levels of[/color]
> > users.[color=darkred]
> > > The "app users" that never see the ID, would not be tracking such an
> > > anomaly.[/color]
> >
> > In other words, the "app users" use a view of the data with a different
> > identifier. They see the identifier they use. They might perceive an[/color]
> anomaly[color=green]
> > in their data and use the identifier they see to track it down.[/color]
>
> True.
>[color=green]
> > The relational model has views and snapshots, ie. named derived relation
> > variables, to address this very issue. It is revealing to consider the
> > candidate keys of these derived relation variables.
> >
> >[color=darkred]
> > > So that is not an issue for us. If such an anomoly were to arise,
> > > which hopefully the design and proper management would prevent[/color]
> >
> > No amount of design and proper management can eliminate human error or
> > malice.[/color]
>
> True, which is why I like the IDENTITY fields in this case. By allowing[/color]
the[color=blue]
> DB to assign a unique key to the records in this particular table, I[/color]
reduce[color=blue]
> the human error component by one by not relying on a user to properly link
> this record to others, or improperly break that link with a typo.[/color]

I direct you back to my earlier suggestion to consider the candidate keys of
the named derived relation variables the "app users" see. From the
perspective of the "app users", you have not affected anything with respect
to forming proper references or identifiers, and you have introduced an
opportunity for human error on the part of the direct dbms users or
application programmers.

Note, I am not saying that one should never use an identity column or
introduce a simple surrogate; I am only trying to get you to recognize the
real consequences of the decision. Those consequences actually contradict
your statement above.

[color=blue][color=green][color=darkred]
> > >, we have a
> > > level of DB users that could analyze the tables directly to solve the[/color]
> > issue[color=darkred]
> > > and hopefully provide a solution to prevent further anomolies.[/color]
> >
> > These users see a different view of the data or perhaps a proper[/color][/color]
superset[color=blue]
> of[color=green]
> > the "app users" view of the data. They, of course, can see the[/color][/color]
identifiers[color=blue][color=green]
> > they use.
> >[/color]
>
> True.[/color]