473,889 Members | 1,437 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 10389
> 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.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 20 '05 #61
On Wed, 3 Dec 2003 09:17:35 -0500 in comp.databases, "Bob Badour"
<bb*****@golden .net> wrote:

With all due respect, the whole point of the witness protection programme is
to prevent people from associating the individual with their previous
identity.


Of course, but for the FBI's database... <g> It was just an example of
a reason for a SSN changing.
--
A)bort, R)etry, I)nfluence with large hammer.
Jul 20 '05 #62
"Bob Badour" <bb*****@golden .net> wrote in message
news:Oq******** ************@go lden.net...

Then I can only conclude you lack the ability to comprehend relatively
simple written english.

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 thing,
then why would we have multiple terms for these things? They are different,
as far as building databases is concerned. Surrogates keys, as defined, are
keys used instead of a natural key. And as far as the following:
That might be a good definition of an IDENTITY column, but it has no bearingon surrogate keys. By equating the identity columns with surrogate keys, youonly confuse yourself and potentially any similarly uneducated readers.
Nothing about a surrogate key requires a DBMS to generate it.
I did not come up with any of this terminology. An identity is an
artificial key. It is used as a surrogate key. It is not a natural key. A
surrogate key is not required to be created by the DBMS, but there is no
reason why we cannot us a mechanism built in to provide it.
I am not my name and my name is not me. My name is a surrogate for me chosenfor simplicity (simple for an english speaker to say), familiarity (familiarfor english speakers) and stability (my name changes rarely).
You name is part of what makes you you, because everyone has a name. It is
technically not a reasonable key, but it possibly part of a key (like the
MPAA does with actor/director names, just by adding a number, or forcing a
name change.)
Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903?

That depends. If I were creating an identifier for a credit card, I would
want at least seven more digits, and yes I would want users typing,
remembering, swiping and dealing with the numbers.
Strangely enough, this is done with credit cards specifically so it will be
hard to rememeber. On the other hand, I would not use a credit card number
as the primary key of a credit card table. Why? Because I would not want
that key spread around the database in other tables. A surrogate key here
would be used to obscure that fact, leaving only a single point of contact
with credit card numbers to be secured.
Preventing the user of the data from seeing the identifier for the data is
just plain stupid.


Yes, I am plain stupid. That is why when you go to a form to choose the
type of credit card you are going to use it shows you 320983902-Visa,
320984902-MasterCard, 320983903-Etc. Or even VS, MC, ET. No, humans like
to see Visa, MasterCard, etc. I am not implying that a user would be
disallowed from using a value, just that there is little reason to ever
present an ugly value to a user, except when that is precisely the desire.

Why I am plain stupid is that I am continuing this discussion and getting
called stupid every few seconds by a person who is not actually having a
discussion that could possibly lead anywhere. If you don't see my point, and
I clearly cannot see your point through all of the muck and anger, then why
waste time. 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.
If it is a matter of taste, then I don't mind anyhow. I like hearing others
opinions, and as to why my ideas are wrong if they are (and some are.)

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

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

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
Jul 20 '05 #63
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:uO******** ******@tk2msftn gp13.phx.gbl...
"Bob Badour" <bb*****@golden .net> wrote in message
news:Oq******** ************@go lden.net...

Then I can only conclude you lack the ability to comprehend relatively
simple written english.
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 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 one is a
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.

That might be a good definition of an IDENTITY column, but it has no

bearing
on surrogate keys. By equating the identity columns with surrogate keys,

you
only confuse yourself and potentially any similarly uneducated readers.
Nothing about a surrogate key requires a DBMS to generate it.


I did not come up with any of this terminology.


That's obvious. You do not comprehend the terminology either.

An identity is an
artificial key.
It is an rdbms generated key.

It is used as a surrogate key.
All keys are surrogates.

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.

A
surrogate key is not required to be created by the DBMS...
Well, then, let's omit that requirement from your earlier definition and see
whether natural keys are surrogates:

"A surrogate key (the word surrogate meaning to take the place of, and the
definition of a surrogate key is: A unique primary key that is not derived
from any [other] data in the database and whose only significance is to act
as the primary key."

I suggest we could replace "the primary key" with "an identifying attribute"
without any loss of meaning and probably gain some clarity. Cleaning things
up a little:

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.

Within many contexts, my name is a unique identifying attribute that is not
derived from any other data and whose only significance is to act as an
identifying attribute within those contexts.

Does that not make my name a surrogate key within those contexts?

My name does not suffice within other contexts. Therefore, I have the nine
digit university identification number I mentioned above and a driver's
license number and a social insurance number and a social security number
and a couple of credit card numbers and a whole bunch of bank account
numbers.

Other than familiarity, what distinguishes a natural key like my name from
any other surrogate?

I am not my name and my name is not me. My name is a surrogate for me

chosen
for simplicity (simple for an english speaker to say), familiarity

(familiar
for english speakers) and stability (my name changes rarely).


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

Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903?
That depends. If I were creating an identifier for a credit card, I would
want at least seven more digits, and yes I would want users typing,
remembering, swiping and dealing with the numbers.


Strangely enough, this is done with credit cards specifically so it will

be hard to rememeber. On the other hand, I would not use a credit card number as the primary key of a credit card table. Why? Because I would not want
that key spread around the database in other tables. A surrogate key here
would be used to obscure that fact, leaving only a single point of contact
with credit card numbers to be secured.
If you worked for a credit card company, you would see things differently.
After all, the credit card number is an account number for the credit card
company. Their whole business relates to those accounts and they use those
numbers to identify the accounts to the external world including to business
partners and to credit bureaus.

Preventing the user of the data from seeing the identifier for the data isjust plain stupid.


If you don't see my point, and
I clearly cannot see your point through all of the muck and anger, then

why waste time.
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 consider
whether this might cloud your ability to interpret the meaning of those
words.

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 congratulate
them for the deed. I suggest you get more out of the gift than the others
do.

If it is a matter of taste, then I don't mind anyhow. I like hearing others opinions, and as to why my ideas are wrong if they are (and some are.)


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 correctly
express queries.
Jul 20 '05 #64
RE/
Please, share your experience in using IDENTITY as PK .


One advantage I see with using IDENTITY PKs (or any blind, dumb number...) is
that I'm protected against my own lack of knowledge.

For instance: I'd guess it's pretty easy to set up a "People" database on the
assumption that social security numbers are unique. The long and short of it
is that they're not. I almost made that mistake once, but my habit of using
surrogate keys whenever possible saved me from getting egg on my face when the
project was about 95% complete.
--
PeteCresswell
Jul 20 '05 #65

"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, they
just know that when they pick Bob's Widgets Inc. in the application, the
data for Bob's Widgets Inc. is presented. Me using an ID under the covers to
link tables is not important to the user.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #66

"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message
news:#G******** ******@TK2MSFTN GP09.phx.gbl...
<snip>
Attributes that are single, [supposedly] unique attributes (e.g., SSN),
usually represent some official, governmentally recognized ID, and therefore have legal issues with being propagated throughout a system.

<snip>

Not to mention SSN's are not unique.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #67

"Aaron Bertrand [MVP]" <aa***@TRASHasp faq.com> wrote in message
news:en******** ******@TK2MSFTN GP10.phx.gbl...
<snip>
Now, you might think, "why not bring SSN into the FirstName + MiddleName +
LastName key? That would make it unique." Yes, and hideously large. If
SSN is unique, then why not just use SSN as the key? Again, it's large even on its own (CHAR(9) and surely to become CHAR(10) in our lifetimes), so I
fail to see the benefit of repeating the value in every related table, DRI
or not.

<snip>

The problem there is that SSN's are not unique. I worked on a project for a
military agency a few years back, and in the requirements analysis phase we
talked about using SSN as our unique ID for some of the data. They were at
that point, coincidenetly, dealing with a problem on another system because
they had 2 officers with identical SSN's. The DBA also told us that this was
4th or 5th time they had seen this in the past 10 years of his career there.

I was shocked to say the least, as I always thought SSN was unique.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #68

"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. :)
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #69

"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.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #70

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

Similar topics

9
2305
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
9961
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...
1
10887
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
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9603
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
7991
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
7148
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
6025
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4644
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4249
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.