473,883 Members | 1,663 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


David Portas wrote:
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.

I guess it depends on where you draw the boundaries of "the system." If
you need something outside of "the system" to verify your key, aren't
you just working within a larger system (and one that is not entirely
under your control)?

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
intrinsicall y better than an arbitrary ID allocated by the server.
This is a good point. If entities enter your system from time to time,
and you must determine whether they duplicate entities already in your
system or are new, then you need some "natural" method of
identification. If I apply for a Microsoft credit card, Microsoft will
likely generate an artifical credit card number for me. But that can't
be the only way in which Microsoft can identify me if a business rule
prohibits one person from possessing two Microsoft credit cards

A nice way to look at this is by recognizing when there is and when
there isn't some external entity that participates in an internal
business rule. The need to use a "natural" key such as my social
security number exists only because there is an entity outside
Microsoft's credit department (the
person-registered-with-the-social-security-administration entity) that
participates in a business rule: that the cardinality of the PRWTSSA <->
CCP relation is required to be 1<->{0,1}.

This is a useful way to look at things. Is an identity value VendorID a
good key for a vendor? It depends on whether there is some entity
"outside the system" with a certain relationship to the entity
identified by VendorID. If there is, then VendorID is not suitable as
the only key. If it is used, it must be a surrogate for a natural
external key also recorded within the system. Some businesses may allow
one corporation more than one VendorID, and others may not.

Is an identity value InvoiceID a good key for invoices (in the database
of the business generating the invoices)? It might well be, since
invoices can be internal to the system. While there might be a more
"natural" key, such as (CreationDateti me, IssuingEmployee _or_System),
the natural key might not provide any added value beyond its individual
attribute values if invoices are entirely internal.

In some cases, what appear to be external entities can use internal
(artificial) keys, because there is no business rule relating the
internal and external entities. An example might be a deli counter
customer who pulls an identity value from the "take a ticket" machine.
Here the only confusion is that there is no handy word for the
human-visit-to-deli-counter entity, and Customer might be a more
convenient name. Despite the fact that customers are people and people
can be identified uniquely outside the system, there's no need to worry
about that to manage visits to the deli counter.

I'm curious now to know whether this point of view helps make a little
more sense of the big debate. If not that, maybe it will at least help
me understand why I get anxious every time I need to use a Microsoft
Passport for identification.

SK



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

--
----------------------------------------------------------------------------
-----------
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 #22

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

1. Natural keys are, being natural and therefore user entered [i.e.,
provided to the database by external means], fungible. If a user enters
data, they must also be able to modify it. If data can be modified, then its
value as a systemic primary key is gone. Yes, you can cascade updates to
these, but why do it when it can be avoided to start with.

2. Natural keys are typically a composite of atomic attributes. If using a
composite, these must be propagated to referencing tables as foreign keys.
Your normalization drops below par, by having these [potentially] massively
duplicated columns.
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.

Also, for amateurs and many professionals, natural keys are very often
chosen incorrectly. e.g., I believe some combination of Name and other info
has been used by my ISP as their primary key. My last name was entered into
their system incorrectly, but they cannot fix it because their system will
not allow it. Preposterous and poor design.

Surrogate keys generated by using the identity property are ideal for data
integrity, because
1. They are static values [i.e., once entered, it does not change] and the
DBA has control over allowing values in identity columns to be modified.
2. They are singleton row ids. The fact that they are sequential is
irrelevant. That is simply the most efficient means of generating new
numeric values.

Identity integers can be problematic in two-way replication, but proper
management of key ranges can alleviate these issues. GUIDs are the MS
recommended way to deal with distributed data and two-way replication, but
not as easy to deal with in unreplicated databases.

Jul 20 '05 #23
On Tue, 2 Dec 2003 09:04:35 -0500 in comp.databases, "Bob Badour"
<bb*****@golden .net> wrote:

"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#r******* *******@tk2msft ngp13.phx.gbl.. .
While it is true that the chosen primary key cannot contain any optional
values, it is more the praticioner (sp?) that disagrees with this stance.
Compound keys are unwieldy and bad for performance, but the theorist in me
says "who cares?" It is all about what is right/best, not what is
fastest/easiest.


Performance is determined by the physical structure and not by the logical
interface. Legitimate theorists have written ad nauseum on the severe
logical problems caused by using compound keys for references when data may
be missing.


Are you talking about data being missing from a foreign key side of a
relationship? I'm pro identity column myself but I don't see how that
would help in this instance.

--
A)bort, R)etry, I)nfluence with large hammer.
Jul 20 '05 #24
On Tue, 2 Dec 2003 17:53:44 -0000 in comp.databases, "David Portas"
<RE************ *************** *@acm.org> wrote:
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
intrinsicall y better than an arbitrary ID allocated by the server.


If Steve leaves that educational facility he's at now, his email
address will surely change. If he goes into a witness protection
scheme his name, address and SS number will change, he or someone else
could change quiet a bit about him but if he's on a database with an
identity column as his PK then it's more likely that it will *never*
change.

--
A)bort, R)etry, I)nfluence with large hammer.
Jul 20 '05 #25


Trevor Best wrote:
On Tue, 2 Dec 2003 17:53:44 -0000 in comp.databases, "David Portas"
<RE*********** *************** **@acm.org> wrote:
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
intrinsical ly better than an arbitrary ID allocated by the server.


If Steve leaves that educational facility he's at now, his email
address will surely change. If he goes into a witness protection
scheme his name, address and SS number will change, he or someone else
could change quiet a bit about him but if he's on a database with an
identity column as his PK then it's more likely that it will *never*
change.

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?

SK



Jul 20 '05 #26

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

I am by no means a SQL expert, so forgive me if this seems ignorant...But
why can't the ID columm be a natural key? For example, I am working on a
project that has a vendors table. The list of vendors is used in
relationship to several other tables. We build this table with an identity
column, and a column with the vendor's name. Now when a vendor is added to
the table, they are assigned a unique ID that ties all other related data
back to this vendor, and in the case of a vendor changing their name, or a
typo, we can make updates without affecting data. Seems like a perfect use
for an identity field, and it is our primary key.
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?



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

Jul 20 '05 #27
> 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 ;)
Jul 20 '05 #28

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

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.


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

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
10410
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
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...
1
4609
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
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.