473,883 Members | 1,688 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
"Greg D. Moore (Strider)" <mo*****@greenm s.com> wrote in message
news:IL******** ************@tw ister.nyroc.rr. com...

"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message
news:uP******** ******@TK2MSFTN GP12.phx.gbl...
And what happens if someone does a DBCC checkident ('FOO', RESEED)?

Or you have to copy it into a new table and accidently set that table up with an IDENTITY column and now all your rows get new IDs?
Fire the DBA. If they've allowed unskilled people such access or do

anything
accidentally, no telling what other problems they'll cause ;)


Wow. Can I get a job where you work where folks never make mistakes?

Seriously, a DBCC checkident can be necessary in some recovery scenarios.

Copying a table over into another one is often necessary in general
maintenance, schema changes, etc. It's pretty easy to forget to do it
right.


I was, of course, being facetious -- well, mostly. :*) Mistakes do happen,
but in most situations in db management, like these mentioned, they are
completely avoidable.

The DBA should be the one doing the DBCC CHECKIDENT - not just "someone".
And if it is an approveed someone else, the DBA better know about it.

Copying a table's data over is often necessary, but you do need to be very
careful and know all the things that might be affected. Again, since this is
a DBA responsibility, he'd better know how to do it right and have some test
db to work with.
[And if that scenario happens, it's pretty easy to fix, although it does
mean moving data all over again.]
Admittedly, they are pretty contrived examples, but the point is, the value of the identity relies on some arbitrary state of the DB at the point in
time it is created.

Now, in some cases that just might not matter, but in many cases it can be
an important factor.


Also very true. But it is the DBA that has [or should have] the control over
any modifications that affect identity values, whereas any user has the
ability to change natural keys [which was the point I was making :)]
Jul 20 '05 #31

"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message
news:uN******** ******@TK2MSFTN GP10.phx.gbl...
"Greg D. Moore (Strider)" <mo*****@greenm s.com> wrote in message
news:IL******** ************@tw ister.nyroc.rr. com...

I was, of course, being facetious -- well, mostly. :*) Mistakes do happen,
but in most situations in db management, like these mentioned, they are
completely avoidable.


Oh I know. :-)

The DBA should be the one doing the DBCC CHECKIDENT - not just "someone".
And if it is an approveed someone else, the DBA better know about it.
Keep in mind not all companies have that level of experience. I've
consulted for a few.

Copying a table's data over is often necessary, but you do need to be very
careful and know all the things that might be affected. Again, since this is a DBA responsibility, he'd better know how to do it right and have some test db to work with.
[And if that scenario happens, it's pretty easy to fix, although it does
mean moving data all over again.]
Actually it's impossible to fix if you've deleted the original table since
you no longer have the original ID numbers.
Admittedly, they are pretty contrived examples, but the point is, the value
of the identity relies on some arbitrary state of the DB at the point in
time it is created.

Now, in some cases that just might not matter, but in many cases it can be an important factor.


Also very true. But it is the DBA that has [or should have] the control

over any modifications that affect identity values, whereas any user has the
ability to change natural keys [which was the point I was making :)]

In an ideal situation, I'd agree.


Jul 20 '05 #32
You have definitely hit a couple of the strong points in IDENTITY's favor.
But there are cases where a "natural" kind of key can be small as well.
Consider when eBay bought PayPal (and other than that, the rest of this
paragraph is completely fictional), they likely had to merge some data...
perhaps eBay used an IDENTITY to generate customer numbers, but they want to
align those primary keys with the new data in the PayPal tables. So, the
keys in the PayPal data become INTs, but not IDENTITY. They are kind of
"natural" because they came to the PayPal from an external source, so to
speak, rather than generated arbitrarily from within.

Of course, completely fictional. But surely you can see that not all
natural keys are going to be larger than an IDENTITY, or less efficient.
There are other examples, too. In a small stats system, a SMALLDATETIME
could be the primary key (perhaps several subrelated tables are organized by
day). In fact, part of http://www.aspfaq.com/stats.asp (and plenty more
that you can't see) is derived on a set of tables where SMALLDATETIME is the
only key of relevance. Okay, so that's still 4 bytes, but you save 4 if
your other alternative is to store an IDENTITY along with the SMALLDATETIME
value. Consider:

CREATE TABLE calendar
(
dateValue SMALLDATETIME PRIMARY KEY
)

vs.

CREATE TABLE calendar
(
dateID INT IDENTITY PRIMARY KEY,
dateValue SMALLDATETIME NOT NULL
)

Never mind my goofy naming scheme. :-)

Now, Kass could probably show me some cool dateadd tricks that would allow
me to store just an INT (or maybe even a SMALLINT, depending on the date
range required), and determine what the date value is at runtime. Not that
I think that's what his argument would be, but rather just to show that it
is still possible to choose either route. I think the usability of the date
value representing what it is, rather than having to derive its value from
some formula, is a good thing.

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.

Firstname + lastname is obviously a bad choice for a key of any kind,
because I know more than one Aaron Bertrand. So then you bring middle name
into the key, and it can still be repeated. Other things like getting
adopted, re-married, legally changing their name, and other reasons why this
"key" would change are minor; changes to the key can be dealt with in the
database using DRI/CASCADE or, worst case scenario, through rigorous update
code; it will be tougher to re-train users to look up all the tables
containing "Carmen Bertrand" instead of "Carmen Electra." :-) However, I
think the possibility of two people having the same key is a far more
compelling argument for bypassing the natural key and placing some
meaningless identifier, like IDENTITY, that the user doesn't care about and
would never have to change.

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.

Sorry about the earful, sometimes I get a little typographical diarrhea.
Hopefully that was at least marginally intelligible.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Stijn Verrept" <sv******@nospa n.vub.ac.be> wrote in message
news:#G******** ******@TK2MSFTN GP10.phx.gbl...
I've read through this thread but I don't understand it. I always use an
int or smallint as primary key, with identity. I believe it would be a mess otherwise.

Example: I have a table with people, last name, first name, address, ... So suppose you would make a natural key then you need at least the last name
and the first name. I have >25 other tables that reference that table. If I get this right I will need to use the name and firstname field in all the other tables as well to reference. Isn't that just a lot of data waste? If I'm missing something, please tell me what because this seems a bit silly.
Stijn Verrept.

Jul 20 '05 #33
> Actually it's impossible to fix if you've deleted the original table since
you no longer have the original ID numbers.


Hopefully you're not starting a DTS task, never mind dropping a table before
validating a successful transfer, without a decent backup in place. I think
this is the kind of thing that Trey means when he uses the term
"avoidable" ... I'll stretch it here to also mean "correctabl e."
Jul 20 '05 #34
"Steve Kass" <sk***@drew.edu > wrote in message
news:3F******** ******@drew.edu ...
In fact none of the 257 different identity values assigned to me will ever
get changed - unless I'm organized and honest, and unless someone
checks some kind of natural key of mine, how will anyone know
it was me each of those 257 times I opened an account?


Those values were not assigned to you, they were assigned to 257 different
accounts. Those 257 accounts will also have 257 natural keys most likely,
which will be an account number, likely taken from a paper form in the bank
office.

If you are talking about you giving 257 different identities (you tell them
you are different people) then the fake people are getting new identity
values, and pretty soon they will get a new artificial key printed on a
fashionable orange jumpsuit :)

--
----------------------------------------------------------------------------
-----------
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 #35
> 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:
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 #36
> 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.


I don't get that impression at all, and I don't believe it is true. Maybe
that's what purists would *like* however...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 20 '05 #37
"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.

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.

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

"Bob Badour" <bb*****@golden .net> wrote in message
news:IO******** ************@go lden.net...
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:e-*************** *****@giganews. com...
> living people than VendorID values. I don't think an identity VendorID > value is any more artificial than any other unique way of
identifying
a > vendor, so long as it is assigned the first time a vendor enters the
> system and is never changed.

But the point is that a "natural" key is verifiable outside of the

system.

As soon as one records the generated identity value outside the system,

the
key is verifiable outside of the system. All keys are surrogates or
artificial keys. Natural keys are nothing more than familiar surrogates.


Jul 20 '05 #38
"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message
news:#G******** ******@TK2MSFTN GP09.phx.gbl...

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:e-*************** *****@giganews. com...
living people than VendorID values. I don't think an identity VendorID value is any more artificial than any other unique way of identifying a vendor, so long as it is assigned the first time a vendor enters the
system and is never changed.


But the point is that a "natural" key is verifiable outside of the system.
When I see a NG post from "skass[at]drew.edu" I don't care whether that's based on your "real" name or even whether S.Kass is the same name as on

your
passport or driver's licence. What's important to me is that it's

determined
by a consistent method outside of the system which gives me some

acceptable
degree of confidence that you're the same person who posted here as
"skass[at]drew.edu" yesterday. Of course that validity is destroyed if you change your email address or if someone spoofs your address. But it's

still
intrinsically better than an arbitrary ID allocated by the server.


I am a firm believer that natural keys should only be used to logically
design/normalize the data. When it comes to the real reason for keys, data
integrity, more often than not I have seen that natural keys are
intrinsically not good physical primary keys.


You are evidently ignorant of the purpose of a key. A candidate key is a
logical identifier that identifies data. It is not a physical identifier. A
dbms can map the logical identifier to any physical identifier using any
method without altering the logical data model.

I suggest an elementary education in data management is in order before you
pontificate on the subject. Spreading your ignorance will only confuse the
unwary novice. Shame on you.
Jul 20 '05 #39
"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message
news:uP******** ******@TK2MSFTN GP12.phx.gbl...
And what happens if someone does a DBCC checkident ('FOO', RESEED)?

Or you have to copy it into a new table and accidently set that table up
with an IDENTITY column and now all your rows get new IDs?
Fire the DBA. If they've allowed unskilled people such access or do

anything accidentally, no telling what other problems they'll cause ;)


Pray your employer never employs the same standard.
Jul 20 '05 #40

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
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...
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...
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...
2
4215
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3231
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.