473,889 Members | 1,414 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 10388
"Trevor Best" <bouncer@localh ost> wrote in message
news:1r******** *************** *********@4ax.c om...
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 logicalinterface. Legitimate theorists have written ad nauseum on the severe
logical problems caused by using compound keys for references when data maybe 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.


The specific issue is compound keys and missing information. A simple key
does not exhibit the same problems regardless whether it is an identity
column.

Consider a compound key with attributes A and B. What happens when the user
inserts a referencing row with a known A and an unknown B? Should the dbms
allow the insert? When should it allow the insert? Should the dbms verify
the A exists at least once in the referenced table? If the A value exists
only once in the referenced table, should the dbms substitute the only
corresponding B value that could be correct in the inserted row? Suppose the
user deletes all the rows from the referenced table that contain a specific
A value. What happens when the referencing table contains corresponding rows
with a known A and an unknown B?
Jul 20 '05 #41
"Trevor Best" <bouncer@localh ost> wrote in message
news:u8******** *************** *********@4ax.c om...
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 yourpassport or driver's licence. What's important to me is that it's determinedby a consistent method outside of the system which gives me some acceptabledegree of confidence that you're the same person who posted here as
"skass[at]drew.edu" yesterday. Of course that validity is destroyed if youchange your email address or if someone spoofs your address. But it's stillintrinsicall 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.


With all due respect, the whole point of the witness protection programme is
to prevent people from associating the individual with their previous
identity.
Jul 20 '05 #42
"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.
Your belief does not alter the correct criteria for choosing a key:
simplicity, familiarity and stability.

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.


You have constructed a straw man. One anecdote does not demonstrate or
justify a general principle or rule.
Jul 20 '05 #43
I see why you were elected a most vociferous person. That's a very long way
of stating something as simple as the criteria for choosing a primary key:
simplicity, familiarity and stability.

"Aaron Bertrand [MVP]" <aa***@TRASHasp faq.com> wrote in message
news:en******** ******@TK2MSFTN GP10.phx.gbl...
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 #44
"Bob Badour" <bb*****@golden .net> wrote in message
news:Od******** ************@go lden.net...
Your belief does not alter the correct criteria for choosing a key:
simplicity, familiarity and stability.
Well non natural keys do meet the simplicity and stability criteria. Even
more than natural keys I believe.
You have constructed a straw man. One anecdote does not demonstrate or
justify a general principle or rule.


Hmmm one anecdote? I have a database full of tables like this. List of
doctors, departments, users, contacts, medication, ... everywhere I used a
non natural key. The use of natural keys will only happen sometimes when
it's really appropriate, otherwise not.
Stijn Verrept.
Jul 20 '05 #45
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 #46
"Trey Walpole" <tr********@SPc omcastAM.net> writes:
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.
This differs from my experience.
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.
Why do it? To avoid duplicates of course. Why not do it? You don't
seem to be making any sort of case here.
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.
I agree that natural keys should be avoided because they're too large.
However, most business reports I see typically have columns that consist
of abbreviations chosen to make the report less wide. These make great
natural keys.
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.
Yes, SSNs are problematic because too many organizations use them for
authentication, i.e. "You put Trey Walpole's SSN on this form, so you must
be Trey Walpole". Even in the absence of such stupid organizations,
privacy advocates oppose national IDs for a very good reason: such IDs
make it easy to create good databases that include people. However, I
assume the original poster had the opposite goal: make it easy to create
good databases.
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.
It sounds like this "prepostero us and poor design" could have been fixed
with a simple REFERENCES ... ON UPDATE CASCADE. Perhaps you should
offer them your services. I can't tell from your story whether or not
they used a poor choice of natural key.

Yes, people can make bad choices as to natural primary keys, but I think
this one additional opportunity to do bad database design is well worth
the risk, given the problems that arise from redundant or duplicate
data.
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.
This is only meaningful if there's something wrong with ON UPDATE
CASCADE, which I think there isn't.
2. They are singleton row ids.


And thus the problem. Earlier this year an e-mail alert system I wrote
was sending two copies when it should just send one. Looking into it,
the employee table had been doubled. I switch to a select distinct to
work around the problem, and someone deleted the duplicates. If we had
a natural primary key for the employee table, I doubt those duplicates
would have gone in.

Primary key constraint errors are your friends.
Jul 20 '05 #47
"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.


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


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

That might confuse the anti-identity fanatics enough so they'll stop
complaining. You will have a natural primary key, so they won't think
the world is coming to an end, but you will go on as you always have,
using the identity column for its convenience in queries, FK
constraints, etc. ;)

SK

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


Certainly, I suppose you could...

CREATE TABLE splunge
(
splungeID INT IDENTITY(1,1) NOT NULL UNIQUE,
email VARCHAR(128) PRIMARY KEY CLUSTERED
)
GO

CREATE TABLE blat
(
splungeID INT NOT NULL
FOREIGN KEY REFERENCES splunge(splunge ID)
)
GO

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 20 '05 #50

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...
0
11187
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
9602
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
5825
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
6021
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
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
4248
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3252
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.