473,854 Members | 1,802 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
Ilija_G (ma****@hotmail .com) writes:
Erland, Thank you for the answare, but maybe I didn't explain quite
well. I need "Select @@identity" after I execute "Insert" command, and
SQL Server execute newid() itself.
Is any simple way to do this, or should I use the the way you suggested
?


There is no corresponding thing to @@identity for GUIDs, so you have to do
it the way I demonstrated.
--
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 #11
Daniel Morgan (da******@x.was hington.edu) writes:
Celko's advice was spot on. The fact that you can't handle a perfectly
valid response without resorting to four letter words demonstrates
many things. Not one of them being expertise in relational databases.
Cut the crap, Morgan! Ilija asked two questions, and Celko's reply
answered none of the questions. If you think that it is a valid response,
then you and I have very different opinions what these newsgroups are
good for. My opinion what I am doing here is clear: to help people and
share my expertise, so that those I can help can improve their skills
and knowledge. You don't do that by flaming them and telling them that
they are idiots, so that they never dare to ask a question again.

Neither you, nor I nor Celko knows what Ilija's business requirements are.
Maybe he should use something else, maybe he have very good reasons for
it. Experience have told me that you need to humble, and realize each
situation has its solution. "Natural keys" may sound oh so fine, but in
many situations of real life, the keys of the real world do not live up
to the requirements of a primary key in a relational database.

Joe Celko writes: Have you ever seen any of my bad days?


We have just see too many of them in these newsgroups. I wonder just how
many poor SQL beginners you have managed to deter from ever asking a
question again with you canned nonsense answers.

--
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 #12
Erland Sommarskog wrote:
Daniel Morgan (da******@x.was hington.edu) writes:
Celko's advice was spot on. The fact that you can't handle a perfectly
valid response without resorting to four letter words demonstrates
many things. Not one of them being expertise in relational databases.

Cut the crap, Morgan! Ilija asked two questions, and Celko's reply
answered none of the questions. If you think that it is a valid response,
then you and I have very different opinions what these newsgroups are
good for. My opinion what I am doing here is clear: to help people and
share my expertise, so that those I can help can improve their skills
and knowledge. You don't do that by flaming them and telling them that
they are idiots, so that they never dare to ask a question again.


There is a difference between what is beign said and how it is being
said. My comment was on the quality of the advice ... not the method.

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
Neither you, nor I nor Celko knows what Ilija's business requirements are.
What makes you think Ilija does either? ;-)

The point being that we give or withhold advice based on what we think
appropriate given what was posted.
Maybe he should use something else, maybe he have very good reasons for
it. Experience have told me that you need to humble, and realize each
situation has its solution.
I'm not knocking humility but you are confusing quality of advice with
how it was given. The usenet is no place for those that lead with their
feelings and emotions.

"Natural keys" may sound oh so fine, but in many situations of real life, the keys of the real world do not live up
to the requirements of a primary key in a relational database.
5% of the time that is True. 95% it is not. Surrogate keys are a 100%
guarantee of data corruption unless you also have unique constraints
that could were, in fact, the natural key.
Joe Celko writes:
Have you ever seen any of my bad days?


We have just see too many of them in these newsgroups. I wonder just how
many poor SQL beginners you have managed to deter from ever asking a
question again with you canned nonsense answers.


I wonder how many of those of you banging out SQL have

1. Never taken a university level course on the subject
2. Have no idea who Chris Date is
3. Wouldn't survive 5 days in a non-windows shop

You'd be wise to listen to Mr. Celko. There are few that knows as
much about the subject as he does. Buy him a scotch some time and
you might get past your view of his attitude and discover his aptitude.

--
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 #13
> Is there any replace for "Select @@identity" that could return "just
inserted" GUID as a primary key?
As Erland stated, you can't retrieve the uniqueidentifie r value when it is
generated via a default constraint or trigger. You can generate the value
yourself in Transact-SQL or generate it in your application program so that
you can easily determine the value.
Has anyone tested what's faster, working with Guid or Autonumber ?
An IDENTITY column is always faster because it is smaller and, when the
column is indexed, results in greater buffer efficiency for inserts due to
the hot spot at the tail of the index. This is especially true when the
index is clustered. Due to the random nature of uniqueidentifie r values,
insert performance degrades when you have a large table with an indexed
uniqueidentifie r column. A non-clustered index mitigates the performance
hit but is still much slower with large tables compared with IDENTITY.

We have an ETL application where we use uniqueidentifie rs to identify
records (sic), assign the values in application code and insert using a bulk
insert technique. Because the resultant tables can have hundreds of
millions of rows, having an indexed uniqueidentifie r column during the
inserts was too much of a performance hit. We either build the index after
the data load or create a composite index with the uniqueidentifie r column
in the last position.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ilija_G" <ac****@on.net. mk> wrote in message
news:40******@n ews.mt.net.mk.. . 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 #14
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.

--
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 #15
Erland Sommarskog wrote:
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.


No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.
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 not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.
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.


This world is populated by adults. Get used to it. Either way ... I may
not have chosen to communicate Celko's thoughts with Celko's words ...
but his advice was more likely to be accurate than any other that could
have been given.

--
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 #16
Daniel Morgan (da******@x.was hington.edu) writes:
Erland Sommarskog wrote:
But that does not mean that anyone who is asking about autonumber values
is one of those people.
No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.


Not only have I followed this newsgroup for quite some time, and I
have also worked with an application for quite some time, and I have
come to the realisation that natural keys out of the real world are
rare creature. Have you ever tried to model financial instruments?

(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.)
I not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.
Ilija may or may not be using is uniqueidentifie r for a good reason.
But he knows what system he is working with, and you and I and Celko
don't. If you still think that you know better than him, that gives
more ideas about your ego than your professional knowledge.
This world is populated by adults. Get used to 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.

And if you think this is "adult" behaviour, then I am not coming over
to your playground.

--
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 #17
Erland Sommarskog wrote:
Daniel Morgan (da******@x.was hington.edu) writes:
Erland Sommarskog wrote:
But that does not mean that anyone who is asking about autonumber values
is one of those people.
No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.

Not only have I followed this newsgroup for quite some time, and I
have also worked with an application for quite some time, and I have
come to the realisation that natural keys out of the real world are
rare creature. Have you ever tried to model financial instruments?


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
intentionally selecting an exception.
(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
professional run IT shop. If you've faced that problem you might wish
to consider that the problem is in your IT department.

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? Do you know
wish to claim you know more than the ASCII committee? How about the
ISO?

You can posture all you wish ... but the vast majority of the time
professionals in our industry will find your advice the quick easy
way out that more often than not demonstrates a lack of formal
education and training.
I not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.


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.
This world is populated by adults. Get used to 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.

--
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 #18
This thread has been an interesting read, to say the least, watching a few
highly-respected Usenet folks "duke it out" on multiple principles. (Notably
Surrogate Keys and Usenet Focus)

Although I'm not in the same league as the aforementioned respectables, I feel
compelled to put in my 2 cents anyway!

I'm familiar with Chris Date and Celko in that I've read their stuff, and I
"seek" their columns with internet searches every now-and-then. I certainly
respect their work. Erland has also helped me in the past.

I frequent Date's and Fabian Pascal's website, dbdebunk.com, admittedly more
for its dry entertainment value, but the site does give ammo to those of us who
try to "do things the right way" in database design for business. (Their
anti-XML stuff is priceless!)

I don't think there's any argument that the "right way" from an ideal design
standpoint is to forget about surrogate keys altogether. (Is there ANY room for
surrogates???)

I gotta tell ya, though, that can be painful. I've got a table with 5 fields in
its primary key, and it has a child (foreign) table with 7 fields in its
primary key (The 5 from the parent, and 2 others.) Yes, I'm actually using a
5-field Foreign Key constraint to hold them together!

I learned databases originally through MS Access, so I would normally have just
said "screw it" and thrown an AutoIncrement in there, and related it using
that, but I have to admit that I was "determined to do this database the right
way" after having been educated by the likes of Celko and Date over the last
few years.

I sometimes regret that decision. It turns out that the "right way" has bogged
down the development time dramatically, because every time I need to reference
those tables, I have to write SQL Statements with ugly joins, long WHERE
clauses, and I have to deal with 5 or 7 fields all the time...passing those
values to-and-from application to server.

If I had used Autoincrement on the parent, related one-many on the child with
that field, development time would have been drastically reduced, and I could
still have used unique indexes judiciously to preserve integrity. I'm really
not sure what I've gained, however, except the right to say "I built it the
right way."

My point is this: Although I strive to do everything "the right way," reality
can interfere with the abstract ideal. I get the impression that the
"Idealists" are so far up the Ivory Tower that they have trouble dealing with
the real world, below the clouds. (Or they are SO Intelligent, Idealistic and
Abstract, they just refuse to--or literally CANNOT--acknowledge reality.)

I'm not being flip. I'm dead serious.

Case in point is this thread. The original post only wanted to know about the
pros and cons of GUID vs Identity. Hell, even the post was TITLED as such!
Erland offered awesome advice toward that post.

But the Idealists--a righteous group at that--couldn't let it go!
(Understandably , too, since it's the nature of righteousness!) It didn't matter
to them that the original post had no questions about when/where/how/why to USE
GUID and/or Identity. It didn't occur to them that the original poster may have
already thought through the ramifications (or inherited the application).

Instead, their retort was quite predictable, questioning mere existence of
GUID/Identity in the schema, while at the same time using subtle "put-downs"
against the implementer.

Personally, I was glad Celko posted what he did. Being righteous about
GUID/Identity is a good thing. I was also glad Erland posted his "cut the crap"
post back at Celko, because he brought to light the 'meaning' of Usenet and the
context of the original question--that is, he brought the post back to the real
world.

Although I consider myself a 'believer' in the relational model, I probably
won't use real keys ever again if they go beyond about 3 fields, maybe 4 at the
end of a one-many-many-many chain! I thank God he gave me the intelligence to
comprehend the Relational Model (well, Set Theory and Logic anyway), but I'm
more grateful that I can use that knowledge in the real world!

Jul 20 '05 #19
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
intentionally selecting an exception.
I didn't take financial instruments out of the blue. This is the core
of the business domain I work with.
(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
professional 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.
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.
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.
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.
--
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 #20

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
9754
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
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...
0
10692
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...
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...
1
7927
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...
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.