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

"Bob Badour" <bb*****@golden .net> wrote in message
news:ru******** ************@go lden.net...
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Qq******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Qa******** ************@go lden.net...
<snip>
With all due respect, the user of the dbms does care. Think about it.
Please explain. I do not understand your point.


That's because you have not thought about it yet. Either that or you

really do lack the ability to comprehend simple written english.


With each post, I believe you to be more of a troll. We are discussing a
specific topic here. You have taken issue with one of my questions. If you
will simply make insulting remarks and continue to refrain from answering
the questions with what you believe to be the correct answer, you do nothing
but reduce your credibility.

I understand the design I am discussing, and believe it to be a good one. If
you believe it not to be, explain why.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #81
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:6f******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:DJ******** ************@go lden.net...
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:p8******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Iu******** ************@go lden.net...
<snip>
> > It is not a natural key.
>
> While the key remains unfamiliar, this is true. However, as soon as it > becomes familiar, this ceases to be the case. I was assigned an arbitrary
> nine digit university identification number two degades ago. I can still > recite this number easily having used it on literally hundreds if not > thousands of pieces of correspondence. That sounds like a natural enough key
> to me.
<snip>

So, are you saying an arbritrary number assigned to you by your collegis a
natural key because it has become famaliar to you, but an IDENTITY
field can't be?
I suggest you apparently lack the ability to comprehend relatively

simple written english as well. Could you point out anything I wrote that would
lead you to conclude I ever said anything so ridiculous?


I did as you ask in my original post, where I quoted your post. It was my
interpretation of that paragraph that led me to ask the question. I was

not making a statement, I was asking a question. You seemed to say, in the above paragraph, that the number assigned to you by your college "sounds like a
natural enough key", so I was inquiring as to why this arbitrary number is
any more natural then an IDENTITY field.


I said nothing to indicate that it is any more natural than an identity
field. I suggest your incapacity with respect to written english amounts to
a failure to apply the closed world assumption.
Jul 20 '05 #82
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:c5******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:ru******** ************@go lden.net...
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Qq******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Qa******** ************@go lden.net...
<snip>
> With all due respect, the user of the dbms does care. Think about it.
Please explain. I do not understand your point.
That's because you have not thought about it yet. Either that or you

really
do lack the ability to comprehend simple written english.


With each post, I believe you to be more of a troll. We are discussing a
specific topic here. You have taken issue with one of my questions. If you
will simply make insulting remarks and continue to refrain from answering
the questions with what you believe to be the correct answer, you do

nothing but reduce your credibility.

I understand the design I am discussing, and believe it to be a good one. If you believe it not to be, explain why.


When the user of the dbms must track down an alleged anomaly in the data,
how does the user proceed?
Jul 20 '05 #83

"Bob Badour" <bb*****@golden .net> wrote in message
news:Dd******** ************@go lden.net...
<snip>
Because most of the contributors to this thread are profoundly ignorant of
fundamentals, they lack the ability to state anything succinctly, and
instead they veer off onto wild tangents where they insist others accept
their ignorant misconceived definitions of simple terms.

<snip>

I guess we'll have to agree to disagree, as I do not see your posts
contributing to the thread, and you don't see me doing that as well.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #84

"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:c5******** ************@gi ganews.com...
That's because you have not thought about it yet. Either that or you

really
do lack the ability to comprehend simple written english.


With each post, I believe you to be more of a troll.


Let me free you of any doubts: if it looks like a troll, smells like a troll
and sounds like a troll: it's a troll!!!
And a boring one too because I hear nothing else but: "lack the ability to
comprehend simple written english.". And I can assure you, I comprehend
simple written English as well as Dutch, French and German :-P
Jul 20 '05 #85
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:-P************** ******@giganews .com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:Dd******** ************@go lden.net...
<snip>
Because most of the contributors to this thread are profoundly ignorant of fundamentals, they lack the ability to state anything succinctly, and
instead they veer off onto wild tangents where they insist others accept
their ignorant misconceived definitions of simple terms.

<snip>

I guess we'll have to agree to disagree, as I do not see your posts
contributing to the thread, and you don't see me doing that as well.


I doubt we disagree on the data management issues, and I am quite content to
agree to disagree on the rest.
Jul 20 '05 #86

"Bob Badour" <bb*****@golden .net> wrote in message
news:vt******** ************@go lden.net...
<snip>
When the user of the dbms must track down an alleged anomaly in the data,
how does the user proceed?


That is a valid point. My answer would be that we have two levels of users.
The "app users" that never see the ID, would not be tracking such an
anomaly. So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent, we have a
level of DB users that could analyze the tables directly to solve the issue
and hopefully provide a solution to prevent further anomolies.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #87
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Ft******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:vt******** ************@go lden.net...
<snip>
When the user of the dbms must track down an alleged anomaly in the data, how does the user proceed?
That is a valid point. My answer would be that we have two levels of

users. The "app users" that never see the ID, would not be tracking such an
anomaly.
In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an anomaly
in their data and use the identifier they see to track it down.

The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.

So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent
No amount of design and proper management can eliminate human error or
malice.

, we have a
level of DB users that could analyze the tables directly to solve the issue and hopefully provide a solution to prevent further anomolies.


These users see a different view of the data or perhaps a proper superset of
the "app users" view of the data. They, of course, can see the identifiers
they use.
Jul 20 '05 #88

"Bob Badour" <bb*****@golden .net> wrote in message
news:7q******** ************@go lden.net...
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Ft******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:vt******** ************@go lden.net...
<snip>
When the user of the dbms must track down an alleged anomaly in the data, how does the user proceed?
That is a valid point. My answer would be that we have two levels of

users.
The "app users" that never see the ID, would not be tracking such an
anomaly.


In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an

anomaly in their data and use the identifier they see to track it down.
True.
The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.

So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent
No amount of design and proper management can eliminate human error or
malice.


True, which is why I like the IDENTITY fields in this case. By allowing the
DB to assign a unique key to the records in this particular table, I reduce
the human error component by one by not relying on a user to properly link
this record to others, or improperly break that link with a typo.
, we have a
level of DB users that could analyze the tables directly to solve the issue
and hopefully provide a solution to prevent further anomolies.


These users see a different view of the data or perhaps a proper superset

of the "app users" view of the data. They, of course, can see the identifiers
they use.


True.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
Jul 20 '05 #89
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Wf******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:7q******** ************@go lden.net...
"BenignVani lla" <bv@tibetanbeef garden.com> wrote in message
news:Ft******** ************@gi ganews.com...

"Bob Badour" <bb*****@golden .net> wrote in message
news:vt******** ************@go lden.net...
<snip>
> When the user of the dbms must track down an alleged anomaly in the data,
> how does the user proceed?

That is a valid point. My answer would be that we have two levels of

users.
The "app users" that never see the ID, would not be tracking such an
anomaly.


In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an

anomaly
in their data and use the identifier they see to track it down.


True.
The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.

So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent


No amount of design and proper management can eliminate human error or
malice.


True, which is why I like the IDENTITY fields in this case. By allowing

the DB to assign a unique key to the records in this particular table, I reduce the human error component by one by not relying on a user to properly link
this record to others, or improperly break that link with a typo.
I direct you back to my earlier suggestion to consider the candidate keys of
the named derived relation variables the "app users" see. From the
perspective of the "app users", you have not affected anything with respect
to forming proper references or identifiers, and you have introduced an
opportunity for human error on the part of the direct dbms users or
application programmers.

Note, I am not saying that one should never use an identity column or
introduce a simple surrogate; I am only trying to get you to recognize the
real consequences of the decision. Those consequences actually contradict
your statement above.

, we have a
level of DB users that could analyze the tables directly to solve the

issue
and hopefully provide a solution to prevent further anomolies.


These users see a different view of the data or perhaps a proper superset of
the "app users" view of the data. They, of course, can see the

identifiers they use.


True.

Jul 20 '05 #90

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
9789
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
10840
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
9570
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
7969
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
5795
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
5989
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4214
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
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.