473,855 Members | 1,864 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Guid vs Identity

Hi,

Is there any replace for "Select @@identity" that could return "just
inserted" GUID as a primary key?

Has anyone tested what's faster, working with Guid or Autonumber ?

Jul 20 '05
24 10895
Erland Sommarskog wrote:
Daniel Morgan (da******@x.was hington.edu) writes:
After 35 years in this industry I've done it more than a few times. And,
as I said before, while there are times when surrogate keys are the
correct solution ... that does not mean that every problem is a nail
and requires the use of hammer: Most don't. But thank you for
intentional ly selecting an exception.

I didn't take financial instruments out of the blue. This is the core
of the business domain I work with.


That may be true. But to either assume everyone else is doing the same.
Or that a specific person is doing so is a huge stretch. Rules have
exceptions. But surrogate keys are the result of rejecting the preferred
approach ... not the initial approach except for the lazy.
(Hint: the situation when a natural key breaks down is when your users
insist on adding something to the system which is not in the domain of
that natural key, but yet is in the domain of the users' business.)


Users don't insist on anything that breaks a system down in a
professiona l run IT shop. If you've faced that problem you might wish
to consider that the problem is in your IT department.


We're an ISV, and there are changes in our customer's business - and
this is an ever-changing world - they can not always wait for our
next release, but have to work around with what they have. Or it
might just be that this particular part of the business is too marginal
to warrant the price tag that we would offer them.


That may well be. And don't get me started on SAP, PeopleSoft or the
worst of the worst ... Siebel.
How is it that Date, Codd, Celko, and many others far more highly
qualified to discuss relational databases than either of us have
managed to not run into this issue as a show-stopper?


Maybe because they have not worked much with real-world system and
gotten dirt under their nails? Nice talk about "the problem is in
your IT department" is alwyas easy to say from an armchair.


Maybe? Perhaps you should learn more about them. Your assumption is not
valid.
Ilija may or may not be using is uniqueidentifie r for a good reason.


And he may not be. But of course that doesn't stop you from jumping in
and defending the quick fix where serious thought might be required.


No, I am not defending "the quick fix". Hell, none of us know if it is
a quick fix or not. You may have 35 years of experience in industry, but
I find that hard to belive. Had you had that experience, you would have
learnt that sometimes you are right, and sometimes you are wrong. And
you should have learnt to not talk about something you don't know about.


My first work in this industry was Fortran IV with punchcards working
on an IBM 370-145 in 1969: You do the math.

What I've learned is that the vast majority of the time the best advice
is what Celko gave. And I clearly stated that his advice was helpful.
Not perfect ... helpful. And should not have received the disrespectful
response you gave it.
If Celko was only pulling the legs of me and other SQL Server MVPs
I could live with it. Now the victims for his meaningless tirades
are innocent people who deserve a better start in the SQL Server
world.


Innocent? No one that takes a job and cashes paychecks for doing a
specific job is "innocent". Try treating what we do as a profession
rather than a hobby.

He is innocent in the sense that just because he gets a paycheck he has
no reason to accept being slammed by nipwits who don't know anything
about his work.


Calling people with decades of expertise nitwits because you don't
share their opinion says much about you. My guess, and it is only a
guess, is that you've never taken a university level academic course
in relational theory or practice. What you've learned you've learned
on the job. And that practice is the reason why so many jobs are
being off-shored to people who, yes work for less, but also know what
they are doing.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #21
--CELKO-- wrote:
Guid or Autonumber .. as a primary key? <<

Did you ever consider not using either and find a REAL key? All you
are doing is using PHYSICAL locators


Except, of course, that the rows may or may not be actually
physically located that way. Given that database tables are
mostly built with B-Trees, they probably aren't located that
way.

Bill

Jul 20 '05 #22
>>I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.<<

What did happen to him? Does he post anywhere else anymore?

Thx,

BZ

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Daniel Morgan (da******@x.was hington.edu) writes:
Far too many people seem to be of the opinion that

1. I have a problem
2. Surrogate keys are a solution
3. Therefore I need surrogate keys


But that does not mean that anyone who is asking about autonumber values
is one of those people.
Neither you, nor I nor Celko knows what Ilija's business requirements
are.


What makes you think Ilija does either? ;-)


You can bet your ass that he knows more about it than we do. We don't
even know his business domain.
I'm not knocking humility but you are confusing quality of advice with
how it was given.


How it was given, indeed has a lot do it. Had Celko posted something
like "In general autonumber or similar are not good solutions for
database implementation" , and continued to explain in friendly voice,
and in away so that even a person with a low experience of database
implemetnation could get an understanding of what he was talking about,
I would not bother. If you want an example who is very good to express
himself in such away, I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.

Jul 20 '05 #23
xAvailx (bj******@hotma il.com) writes:
I encourse you to search Google groups for posts

by BP Margolin who unfortunately does not post here any more.<<

What did happen to him? Does he post anywhere else anymore?


I have no idea of his whereabouts. If you see him somewhere, please
say hello from me!

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #24
Daniel Morgan (da******@x.was hington.edu) writes:
That may be true. But to either assume everyone else is doing the same.
Or that a specific person is doing so is a huge stretch. Rules have
exceptions. But surrogate keys are the result of rejecting the preferred
approach ... not the initial approach except for the lazy.
It's funny, though, that when I take something from my own business that
you immediately admit that this is an exception. I'll give you another
core item in our application which appears equally difficult to model
with natural keys: customers.
What I've learned is that the vast majority of the time the best advice
is what Celko gave. And I clearly stated that his advice was helpful.
Not perfect ... helpful. And should not have received the disrespectful
response you gave it.
Let's see, Celko gives a very disrespectful response to Ilija, and
when I criticize that, you complain that I am being disrespectful?
Calling people with decades of expertise nitwits because you don't
share their opinion says much about you.
You are nipwits becase you are slamming someone (or defends the
slamming) without nothing zilch about what he is doing. And just because
you can call yourself an expert in relational databases does not
defend that. You are not an expert in Ilija's business, but complete
ignorants.
My first work in this industry was Fortran IV with punchcards working
on an IBM 370-145 in 1969: You do the math.


This is not a discussion about math. This is a discussion about social
skills. Of which one would expect that your 35 years in industry
should have helped you to acquire some.

You see: that is the core of the discussion *How* it is being said.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #25

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
630
by: Robert Chapman | last post by:
I know that this should be impossible, yet it is happening... I have some c# code that uses a method scoped variable of type Guid. The code assigns Guid.NewGuid() to the variable. This value is then used as a primary key in a database table. I have seen two occasions over the last 6 months where after a certain point in time the same Guid is always generated by the code. I have verified that there is nothing static, the code is...
0
1428
by: Armel Asselin | last post by:
Hello, I try to use an GUID identity column (GUID / Automatic Number). Previously I used a Long / Automatic Number and I could use "SELECT @@identity as ID" to retrieve the ID of each newly created line. What is the request to use when the identity is a GUID column ??? Please help Armel
26
11582
by: John Grandy | last post by:
Is it possible to generate a 20 byte integer from a GUID that is "unique enough" ( just like a GUID is not truly unique , but is "unique enough" ). We identify transactions with GUIDs , but a partner web service has a 20 byte limit on transaction ID passed.
9
10284
by: eagle | last post by:
How can I return the new id that is created when an insert command is used? for example: qry = "insert into tblClients (lname, fname) values ('smith', 'joe')" Dim xyz as string = command.executescalar(strconn, commandtype.text, qry) I tried this and received an error "System.NullReferenceException: Object reference not set to an instance of an object" Although the insert does take place. How would I do this? I can't create a
5
6680
by: Jason L James | last post by:
Hi all, is there a method or property of a class that will return a GUID in VB.NET that I can use as the PK of my DB. I do not want SQL to generate it as I can not then use SELECT @@IDENTITY to retrieve it. I want to try and mimic the functionality of
3
3044
by: MP | last post by:
context: vb6/ ado / .mdb format / jet 4.0 (not using Access - ADO only) - creating tables via ADO (don't have access) - all tables have a primary key (PK) - many of the PK will become FK(Foreign Key) in other table(s) - record entries will be made via ADO I am soliciting opinions on the pros and cons of using AUTOINCREMENT versus code generated GUID or LONG value (as far as my limited understanding goes, if I enter a record, and need...
1
20991
by: Brad Eck | last post by:
In Access, newID returns a unique for the table. In SQL Server, newid() returns a GUID - unique in the world. I do not need or desire that complexity. Is there a way to get a simple unique int on the table in SQL Server? Brad Eck http://www.sitesdynamic.com http://www.basketsetcetera.com *** Sent via Developersdex http://www.developersdex.com ***
8
8751
by: Jack Brown | last post by:
Hi there, I need to create a unique identifier for my own internal needs and am happy to rely on "Guid.NewGuid()" to pull this off. I'd like to know if .NET offers any competing alternative however. Perhaps there's something with a richer set of functions for instance and it might offer potential advantages over a GUID. Am just exploring the possibilities for now. Thanks in advance.
16
2394
by: sloan | last post by:
Current Framework 2.0/3.0. ... In Sql Server, there is a way to generate consecutive guid's. newsequentialid. Is there a way to reproduce this type of consecutive guid's in the framework.
0
9903
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
11044
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
10767
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
10375
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
9526
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
5952
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4567
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
4168
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3194
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.