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

--
----------------------------------------------------------------------------
-----------
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:pf******** ************@go lden.net...

"Bruce Lewis" <br*****@yahoo. com> wrote in message
news:nm******** *****@scrubbing-bubbles.mit.edu ...
ne********@hotm ail.com (Andy) writes:
What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000? Please, share your experience in using IDENTITY as PK .
My experience says the theorists are right ...

...
I'd personally recommend going with natural primary keys, even if
they're compound.


The theorists disagree with you with respect to compound primary keys --
especially in SQL. Candidate keys obviously have as many attributes as

they have, but forming references with compound keys causes severe problems when information may be missing.

Jul 20 '05 #11

"Louis Davidson" <dr************ ***@hotmail.com > wrote in message
news:#r******** ******@tk2msftn gp13.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.

-------------------------------------------------------------------------- -- -----------
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:pf******** ************@go lden.net...

"Bruce Lewis" <br*****@yahoo. com> wrote in message
news:nm******** *****@scrubbing-bubbles.mit.edu ...
ne********@hotm ail.com (Andy) writes:

> What are cons and pros for using IDENTITY property as PK in SQL SERVER > 2000? Please, share your experience in using IDENTITY as PK .

My experience says the theorists are right ...

...
I'd personally recommend going with natural primary keys, even if
they're compound.


The theorists disagree with you with respect to compound primary keys --
especially in SQL. Candidate keys obviously have as many attributes as

they
have, but forming references with compound keys causes severe problems

when
information may be missing.


Jul 20 '05 #12
> "who cares?" It is all about what is right/best, not what is
fastest/easiest.


Unfortunately, what is right/best is not always among the criteria when the
work is for someone else.

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

"Bruce Lewis" <br*****@yahoo. com> wrote in message
news:nm******** *****@scrubbing-bubbles.mit.edu ...
ne********@hotm ail.com (Andy) writes:
What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000? Please, share your experience in using IDENTITY as PK .


My experience says the theorists are right about the dangers of an
artificial primary key. Many real-world database problems stem from
duplicates that would never have been there with a natural primary key.
Natural primary keys also result in reports with fewer joins.

As for the pros of IDENTITY, if you are going to use an artificial
primary key, that's the way to do it. Triggers don't work as well. A
pro for artificial keys in general is that Microsoft products make
compound primary keys inconvenient. Transact-SQL doesn't have tuple
comparisons, e.g. (a, b, c) = (x, y, z), making joins inconvenient.
ASP.NET components that do DataBind() don't handle compound keys at all.
AFAICT, you can only set a KeyColumn parameter to a single column.

I'd personally recommend going with natural primary keys, even if
they're compound.


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


An identity value that is generated by the system is not "natural".. . a
natural key means that the key is, by nature, identifying a single row...
not artificially because you generated some value for it. A natural key
could be an e-mail address, or a social security number, or a license plate
number, or a latitude and longitude -- something that is part of the data
that also happens to uniquely identify it.

Keep in mind that a primary key does not have to a natural key. I don't see
any problems with using an IDENTITY as a primary key, such as in your case.
But in your case it is not a natural key.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 20 '05 #15
IDENTITY may be a *surrogate* key but it isn't a *natural* key because it
bears no relation to the entity that you are modelling in your table. A
natural primary key is a subset of the attributes of an entity which
uniquely identify that entity. IDENTITY clearly isn't an attribute of any
real entity - it's just an arbitrary number.

Taking your Vendors table as an example, a naive design might look like
this:

CREATE TABLE Vendors (vendor_id INTEGER IDENTITY PRIMARY KEY /* ?? */,
vendor_name VARCHAR(40) NOT NULL, vendor_tax_id VARCHAR(10) NOT NULL, ...)

But this table has no uniqueness or integrity because multiple vendors can
exist with different (arbitrary) vendor_ids. It may well work internally for
a particular application but will break when someone needs to do some real
analysis on your data. And what if you need to combine it with data from
another system that doesn't have that same magical Vendor_id column?

Here's a better alternative:

CREATE TABLE Vendors (vendor_id INTEGER NOT NULL UNIQUE /* surrogate */,
vendor_name VARCHAR(40) NOT NULL UNIQUE, vendor_tax_id VARCHAR(10) NOT NULL
PRIMARY KEY, ...)

By declaring UNIQUE / PK constraints on the correct attributes you can
ensure that you have verifiably unique data. Keep the surrogate key if you
like but make sure you declare the Natural key as well. (Moving the actual
PK declaration is essentially cosmetic - PK is equivalent to NOT NULL UNIQUE
and it's not unusual to have several NOT NULL UNIQUE keys in a table).

Here's Celko on keys:

http://www.intelligententerprise.com...celko1_1.shtml

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #16


Aaron Bertrand - MVP wrote:
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
relationshi p to several other tables. We build this table with an identity
An identity value that is generated by the system is not "natural".. . a
natural key means that the key is, by nature, identifying a single row...
not artificially because you generated some value for it. A natural key
could be an e-mail address, or a social security number, or a license plate
number, or a latitude and longitude -- something that is part of the data
that also happens to uniquely identify it.

Quiz: Classify each key below as "natural" or "artificial ":

Northwind..Cust omers.CustomerI D
Northwind..Orde rs.OrderID
Northwind..Terr itories.Territo ryID

Most keys are fundamentally artificial, but somehow we only call them
artificial if we made them up, not if someone else made them up. Social
Security numbers are probably nothing different than identity values in
someone elses database, which doesn't make them any more intrinsic to
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.

I think this whole natural/artifical distinction is mostly quite silly.
In a well-designed database, entities can just as easily be identified
by an integer they are assigned when they enter the system as they can
by some set of attributes within the data they enter the system with.
The arguments against using identity values always seem to be arguments
against the ways people misuse identity values.

The advantage of keys like Northwind..Cust omers.CustomerI D is really an
error-correction issue. If carefully chosen, those 5-character keys can
be recovered if a single letter is mistyped. But this can be done with
artificial values also, if check digits or other error-correcting
schemes are used.

SK

Keep in mind that a primary key does not have to a natural key. I don't see
any problems with using an IDENTITY as a primary key, such as in your case.
But in your case it is not a natural key.


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

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #18
> artificial if we made them up, not if someone else made them up. Social
Security numbers are probably nothing different than identity values in
someone elses database,


Right, but this is a centralized and controlled database. Nobody else has
my SSN (though there are exceptions, e.g. someday SSNs for deceased people
will have to be re-used); anybody else who uses it to identify themselves is
likely attempting fraud / identity theft. I consider it a "natural" key
because I supply it to the database, rather than the other way around.

Whereas my customerID according to Barnes & Noble is very unlikely to be the
same as my customerID at J.Crew.

In any case, I do agree that the distinction is largely silly, especially
when it erupts into arguments and "but Celko says..." nonsense. Like many
other things in the database world, the choice of a key is not dictated by
some higher power, but is rather situation-dependent.

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

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
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...
1
7970
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
7123
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...
0
5990
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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.