473,883 Members | 1,596 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 10383
"Steve Kass" <sk***@drew.edu > wrote in message
news:3F******** ******@drew.edu ...

Stijn Verrept wrote:
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:Nv******* *************@g iganews.com...
Do you really allow the same Doctor, Department, etc to appear twice in

its

table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key

at

all unless the table also has a natural key - it's just a physical row
identifier.


I never said I allow them to appear twice in the column, you have Unique
Constraint for that. I could use that as a natural key, but I prefer usingan int or smallint. I don't want to note Name, Firstname, ... in another
table as foreign key! Also in the application I don't see me writing:
select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName =:FirstName) and (SN_BirthDate = :SNBirthDate).
Stijn Verrept.

How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
column and putting the PRIMARY KEY NONCLUSTERED constraint on the
multi-column primary key?


Your question demonstrates profound confusion between logical and physical.
Uniqueness is a logical constraint. Clustering is purely physical and is an
attribute of an index not of a constraint. I realize that SQL confuses the
issue by inappropriately making uniqueness a property of a physical index
structure, but I see no reason to further confuse the issue.
Jul 20 '05 #51


Bob Badour wrote:
"Steve Kass" <sk***@drew.edu > wrote in message
news:3F******* *******@drew.ed u...

Stijn Verrept wrote:
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:Nv***** *************** @giganews.com.. .

Do you really allow the same Doctor, Department, etc to appear twice in


its


table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key


at


all unless the table also has a natural key - it's just a physical row
identifie r.


I never said I allow them to appear twice in the column, you have Unique
Constraint for that. I could use that as a natural key, but I prefer

using

an int or smallint. I don't want to note Name, Firstname, ... in another
table as foreign key! Also in the application I don't see me writing:
select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName

=

:FirstName ) and (SN_BirthDate = :SNBirthDate).
Stijn Verrept.


How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
column and putting the PRIMARY KEY NONCLUSTERED constraint on the
multi-column primary key?


Your question demonstrates profound confusion between logical and physical.
Uniqueness is a logical constraint. Clustering is purely physical and is an
attribute of an index not of a constraint. I realize that SQL confuses the
issue by inappropriately making uniqueness a property of a physical index
structure, but I see no reason to further confuse the issue.

Then don't.

Or else be honest. If you see no reason to confuse the issue further,
why quote me out of context by deleting my next paragraph, which made it
clear I was joking? [In case it's not clear, this is a rhetorical
question. I don't really want an answer from you.]

SK




Jul 20 '05 #52

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

Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903? I certainly
don't. Invariably they would want something that they understood. Or what
about a GUID: 6969B66E-6A7A-4E89-B2D9-B35799B335C1 vs
DCF5DBC5-73B5-4009-9BBC-9312CFD6AD9D. Yick.

Kind of like you have a user name, an email address, fingerprints, an SSN
and DNA, etc that all identify you, but you only use a few of them here.
DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
is, but it still exists.
Natural keys are nothing more than familiar surrogates.


You are kind of right here, but it is generally true that natural keys can change, because in the world, things can change. Identities cannot

change.

It is not a "kind of" rightness. The statement is obviously and
self-evidently right to anyone with at least a minimal education in the
fundamentals of data management.

I suppose. But I don't think that the statement means anything. I don't
agree that all natural keys are familiar surrogates. Not every natural key
was initally randomly chosen. Some level of thought was placed to choosing
a name, hence it is not just a familar surrogate. A surrogate key (the word
surrogate meaning to take the place of, and the definition of a surrogate
key is: A unique primary key generated by the RDBMS that is not derived from
any data in the database and whose only significance is to act as the
primary key. I would remove the word primary from the sentence and we have
what I would agree with the definition)

They are very much alike, but I don't think you can state that a natural key
is a surrogate, ever. They are both keys, in that they both are
determinants, which is why we have called them keys.
--
----------------------------------------------------------------------------
-----------
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 #53
"Stijn Verrept" <sv******@nospa n.vub.ac.be> wrote in message
news:OF******** ******@TK2MSFTN GP11.phx.gbl...
In cases like e-mail address and SSN (and in fact most cases), I still
prefer your route, where there is a surrogate key (IDENTITY) that prevents me from having to cascade changes all over the place, and store larger
foreign keys.
Ok I agree that sometimes natural keys are good. But mind the word:


NATURAL KEYS ARE ALWAYS GOOD. I was standing on my desk shouting if you
didn't see it :)

Just not always preferrable as PRIMARY KEYS! Always put keys on EVERY
unique combination (that doesn't include other unique combiniations ( if ID
is a unique key, then ID, Name should not be, use a simple index in this
case) for the sake of your data. Look up Boyce Codd normal form for more
information.

--
----------------------------------------------------------------------------
-----------
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 :)

sometimes :). After reading through this thread I got the impression that
natural keys are used more than surrogate keys, while in practice I use
identity with surrogate keys almost all the time.
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.


Indeed, and I don't know about American legislation but maybe in the

future (or even now) you can have people work for you who don't have a SSN (who
work from a distance country for example) and then you'll get stuck again.
Sorry about the earful, sometimes I get a little typographical diarrhea.
Hopefully that was at least marginally intelligible.


It was :)
Stijn Verrept.

Jul 20 '05 #54
BINGO! This is the point that seems to be missed by most every one. Thank
you David

--
----------------------------------------------------------------------------
-----------
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 :)

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:Nv******** ************@gi ganews.com...
Do you really allow the same Doctor, Department, etc to appear twice in its table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key at all unless the table also has a natural key - it's just a physical row
identifier.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #55
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#7******** ******@TK2MSFTN GP09.phx.gbl...

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


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.

Preventing the user of the data from seeing the identifier for the data is
just plain stupid.
Jul 20 '05 #56
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#7******** ******@TK2MSFTN GP09.phx.gbl...

"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...
Kind of like you have a user name, an email address, fingerprints, an SSN
and DNA, etc that all identify you, but you only use a few of them here.
DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
is, but it still exists.


You seem ignorant of a few basic facts regarding DNA. Multiple individuals
(identical twins as well as artificial clones) largely share
indistinguishab le DNA. Almost all individuals have multiple DNA patterns due
to viral infections and random mutations. Some individuals, chimeras, have
multiple DNA patterns that are very different. The use of stem cell
treatments will introduce additional DNA patterns into individuals.

Before I die, I hope that none of my cells have my original DNA. Of course,
I also hope that won't be for several millenia at least.

Oh, and DNA fails the simplicity criterion for effective logical references.
Jul 20 '05 #57
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#7******** ******@TK2MSFTN GP09.phx.gbl...
"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...
> Natural keys are nothing more than familiar surrogates.

You are kind of right here, but it is generally true that natural keys can change, because in the world, things can change. Identities cannot change.

It is not a "kind of" rightness. The statement is obviously and
self-evidently right to anyone with at least a minimal education in the
fundamentals of data management.

I suppose. But I don't think that the statement means anything.


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

I don't
agree that all natural keys are familiar surrogates. Not every natural key was initally randomly chosen.


No key is randomly chosen and nothing in surrogacy implies or suggests
randomness. A truly random choice would suggest a psychotic break with
reality. Nothing that anyone can use to identify me is me, and in that sense
any key that identifies me is only a surrogate for me.

Only values are self-identifying, but we use representations as surrogates
even for values.
Jul 20 '05 #58
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#7******** ******@TK2MSFTN GP09.phx.gbl...

"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... Some level of thought was placed to choosing
a name, hence it is not just a familar surrogate.


Again, I suggest the above sentence suggests you lack the ability to
comprehend relatively simple written english. Perhaps, if you opened a
dictionary and looked up the word 'surrogate', you might improve your
competence at extracting meaning from english.

A surrogate implies no particular level of thought.

A surrogate key (the word
surrogate meaning to take the place of, and the definition of a surrogate
key is: A unique primary key generated by the RDBMS that is not derived from any data in the database and whose only significance is to act as the
primary key. I would remove the word primary from the sentence and we have what I would agree with the definition)


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.
Jul 20 '05 #59
"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#7******** ******@TK2MSFTN GP09.phx.gbl...
"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... They are very much alike, but I don't think you can state that a natural

key is a surrogate, ever. They are both keys, in that they both are
determinants, which is why we have called them keys.


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

Beyond that, I have been well conditioned to respond to my name, which makes
it useful for those who wish to get my attention. Although, given the
similarity in pronunciation to "Mom", this conditioning can be somewhat
inconvenient at the mall and in restaurants.
Jul 20 '05 #60

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
9936
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
9791
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10845
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
10412
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
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...
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.