473,883 Members | 1,674 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Jul 20 '05
112 10380

"Bob Badour" <bb*****@golden .net> wrote in message
news:Iu******** ************@go lden.net...
<snip>
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 arbitrary
nine digit university identification number two degades ago. I can still
recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural enough

key to me.

<snip>

So, are you saying an arbritrary number assigned to you by your collegis a
natural key because it has become famaliar to you, but an IDENTITY field
can't be?

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #71
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:a7******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Z-*************** *****@golden.ne t...
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:ex******** ******@tk2msftn gp13.phx.gbl...
The problem is with how it is used. If you start giving users access to identity based values, you get into a bad spot where they want to make
changes to the value (in my line of work, we don't like the numbers 666
in
account numbers) so using identities for user values is a bad idea. I use them only for internal pointers that are never presented to users,
since they are not modifiable. I could use guids, or characters, or
whatever for
keys and no one would be the wiser.


Keys are logical identifiers. They identify data for the user as well as

for
the dbms. Preventing the user from seeing the identifier is just stupid.

<snip>

I disagree. In the example I presented at the top of this thread, I use a
vendorID that is used to link the normalized tables to each other. The

user does not care that Bob's Widgets Inc. is number 12345 in the database,


With all due respect, the user of the dbms does care. Think about it.
Jul 20 '05 #72
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:a7******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:-f************** ******@golden.n et...
<snip>
With all due respect, the whole point of the witness protection
programme is
to prevent people from associating the individual with their previous
identity.


So a bad PK would be prefered. :)


Nope. Two identities require two identifiers. A good key will capture that
information correctly.
Jul 20 '05 #73

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:a7******** ************@gi ganews.com...

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:#f******** ******@TK2MSFTN GP11.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 no interest 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
deconstructioni st 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?
Jul 20 '05 #74
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:p8******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Iu******** ************@go lden.net...
<snip>
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 arbitrary nine digit university identification number two degades ago. I can still
recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural enough

key
to me.

<snip>

So, are you saying an arbritrary number assigned to you by your collegis a
natural key because it has become famaliar to you, but an IDENTITY field
can't be?


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?
Jul 20 '05 #75

"Bob Badour" <bb*****@golden .net> wrote in message
news:Qa******** ************@go lden.net...
<snip>
With all due respect, the user of the dbms does care. Think about it.


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
Jul 20 '05 #76
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Qq******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Qa******** ************@go lden.net...
<snip>
With all due respect, the user of the dbms does care. Think about it.


Please explain. I do not understand your point.


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

"Bob Badour" <bb*****@golden .net> wrote in message
news:J-*************** *****@golden.ne t...
<snip>
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
deconstructioni st 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.
That is very useful. If you didn't have an apparent knowledge of DBMS's I'd
believe you were a troll.
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?


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
Jul 20 '05 #78

"Bob Badour" <bb*****@golden .net> wrote in message
news:DJ******** ************@go lden.net...
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:p8******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Iu******** ************@go lden.net...
<snip>
> 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 arbitrary nine digit university identification number two degades ago. I can still recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural
enough key
to me.

<snip>

So, are you saying an arbritrary number assigned to you by your collegis a natural key because it has become famaliar to you, but an IDENTITY field
can't be?


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?


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
Jul 20 '05 #79

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:6f******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:J-*************** *****@golden.ne t...
<snip>
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
deconstructioni st 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.


That is very useful. If you didn't have an apparent knowledge of DBMS's

I'd believe you were a troll.
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?
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.


"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?
Jul 20 '05 #80

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2304
by: Phil W | last post by:
Hi all, Am having a bit of trouble with the @@identity field - I probably just have that friday feeling and am missing off something obvious, but the below code brings back am empty identity value ("sid" appears empty). I've definitely set up an identity field in the tblSurvey: set rsAdd = Server.CreateObject("ADODB.Recordset") rsAdd.open "tblSurvey", conn, 3 , 3 rsAdd.AddNew
3
11446
by: Mark | last post by:
Hi, How to add a foreign key constraint using the SQL server 2000 enterprise manager? Not by SQL. thanks
0
9935
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10742
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10844
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9571
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7970
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7122
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5797
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5990
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4215
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.