| Author |
Topic |
|
natas
Yak Posting Veteran
51 Posts |
 Posted - 2005-08-29 : 23:08:29
|
Hello dudes  , how ya all doing?.I have a good question for ya all. i have a table that looks like this:CREATE TABLE customers(customersID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,customername varchar(255))Now, how do i retrieve the last inserted GUID?.if the PRIMARY KEY where an "int" datatype and a IDENTITYit would be easy, i would just use the SCOPE_IDENTITY() function.But in this case that one doesnt work because IDENTITY cannot handle GUID values.. so what do i do?. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-08-29 : 23:48:42
|
| You can't do it like that. Your best bet is create the GUID first.I.e.Declare @customerID UNIQUEIDENTIFIERSET @customerID = NewId()INSERT etcDamian"A foolish consistency is the hobgoblin of little minds." - Emerson |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-08-30 : 09:10:27
|
| OK thanks. How common is it that database designers use GUID instead of IDENTITY?. Are ppl starting to use GUID more now ?. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-30 : 10:24:30
|
quote:
OK thanks. How common is it that database designers use GUID instead of IDENTITY?. Are ppl starting to use GUID more now ?.
My opinion:If you just want to generate a surrogate key, integers are better. They're better for indexes and they certainly are easier to refer to for ad-hoc queries. The only pros I can come up with are that guids would be good for when you want to generate a unique value from multiple sources without any collisions. Also maybe (and this is a stretch but) if you intentionally want to use an id value that is hard for humans to deal with.Be One with the OptimizerTG |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-30 : 10:25:23
|
| It's not a question of "GUID instead of IDENTITY".They have different uses (and different pros & cons).When You need to generate keys that are globally unique (replication/distributed systems), guid's can have their place.Identity columns are basically only unique in the current database, but smaller and more efficient.Have You heard of "Natural Keys" ?Ask the database designer why he used guids, there should be some reason !?Or maybe not.-- to sum it up it depends -- |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-30 : 10:34:55
|
SCOPE_GUID()?Just kidding  Bit of a long shot:Would the "NewID()" GUID be available in "inserted" in an INSERT trigger? Any way to get it back from there to the process that inserted it?Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-30 : 11:58:03
|
| Well the answer is of course to reselect the GUID of the newly inserted rowby it's natural key, and return it to the client.Billy the Guid |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-08-30 : 13:52:11
|
| Rockmoose, I dont like natural keys , i always use surrogate keys. Because i think like this:If im using natural keys as my primary keys, im making the info that im about to store in the database part of the database structure, i think that the info that is stored in the database should be completely separated from the database structure, no exception.If im using natural keys i also have to find a uniuqe real life value for every table that i want to create, it can be real hard if say for example some customers are named exactly alike, and the table has to accept customers that are named the same, and so on and so on..And there are reasons for not using IDENTITY for example when doing some web service stuff, when u first download a couple of rows from the database to you windows client, and do some editing, and inserting data on it, and then send it back to the database, if another dude has made some inserts and have already send back those inserted rows into the database and he received fresh IDENTITY:s back, and you also have done some inserts on your client side but havent yet sended them back to the server, your client IDENTITY:s wont match those on the server.. That is a reason to use GUID:s instead. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-30 : 14:05:28
|
oh oh...I sense an impending surrogate vs natural keys war.quote:
And there are reasons for not using IDENTITY for example when doing some web service stuff, when u first download a couple of rows from the database to you windows client, and do some editing, and inserting data on it, and then send it back to the database, if another dude has made some inserts and have already send back those inserted rows into the database and he received fresh IDENTITY:s back, and you also have done some inserts on your client side but havent yet sended them back to the server, your client IDENTITY:s wont match those on the server.. That is a reason to use GUID:s instead.
Whatever side you're on, I think (the previous quote) is a terrible argument against identities because:I think its a bad application design that relies on client generated identity values surviving the posting process.Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-30 : 14:19:42
|
| I searched for it, but I couldn't find it. Anyone got a link to the thread where Paul Randal from Microsoft says not to use GUIDs as PKs?Tara |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-08-30 : 14:23:23
|
quote:
oh oh...I sense an impending surrogate vs natural keys war.Whatever side you're on, I think this is a terrible argument against identities:
I use IDENTITY and GUID:s, where did say i like natural keys better?.Can you explain further what you mean, and "a terrible argument" doesnt say alot about what you mean.quote:
I think its a bad application design that relies on client generated identity values surviving the posting process.
i said that GUID:s can solve this web service issue if the GUID:s are created on the client and not on the server. and "bad application design"?, explain in detail what you mean, and explain in detail how you would solve this web service action without using GUID:s and not using natural keys:Say you have 2 tables, 1 parent and the child of that parent. The client inserts a row in the parent and then a row in the child, but he havent saved it back to the database yet, and remember 100 ppl are doing this at once. Please explain how you would solve this issue. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-30 : 14:33:59
|
| Sorry natas that wasn't very clear. I edited my previous post to clerify my objection. I didn't mean to imply you like natural keys.Be One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-30 : 14:41:04
|
quote:
i said that GUID:s can solve this web service issue if the GUID:s are created on the client and not on the server. and "bad application design"?, explain in detail what you mean, and explain in detail how you would solve this web service action without using GUID:s and not using natural keys:Say you have 2 tables, 1 parent and the child of that parent. The client inserts a row in the parent and then a row in the child, but he havent saved it back to the database yet, and remember 100 ppl are doing this at once. Please explain how you would solve this issue.
Admittedly, I haven't solved that issue. I worked around it by forcing the user to save the master record before being allowed to entery child records. So I never actually dealt with your specific issue.Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-30 : 14:58:10
|
| That Paul Randal thread is awesome!>>Please explain how you would solve this issue.I don't know, maybe write a post routine that first saved the master then (after successfully getting the DB generated identity) post the children with that identity rather than the one my application assumed would be the next value. Or maybe send all the data in one SP and let the SP deal with posting the master then the children within a transaction.Be One with the OptimizerTG |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-08-30 : 15:29:53
|
quote:
Admittedly, I haven't solved that issue. I worked around it by forcing the user to save the master record before being allowed to entery child records. So I never actually dealt with your specific issue.
If you only have 1 parent and 1 child it is acceptible to do this. But there is no databases that only consist of 1 parent and 1 child. They ususally have a very strong hierarchy that consist on several related tables, parent-child-grandchild-grandgrandchild-grandgrandgrandchild and so on and so on, so, so that would require to have somewhat 10 save buttons and forcing him/her to hit each of those tables save button when the user has inserted something in them. An application should consist of only 1 save button, having more would only confuse the user. And i dont think that it is a good web service design neither to just send back 1 row at a time to the database, so if the user do 20 inserts (both in parent and childs) that would require 20 calls to the web service instead of just 1. So as i said, GUID:s can solve both this issues in a flash right on dude , INSERT:ing all of the PRIMARY GUID KEYS on the client and relating them in the clients FOREIGN KEY INSERT:ed tables, and then hit 1 save click and implement the same PK-FK structure and the same GUID numbers you have on your client on to the server database, and the integrity is intact because there is little chance that 2 users generates the same GUID, dont you now agree considering GUID:s in this scenarios?. GUID is lovely , ohyes  .. But i still agree that IDENTITY is the nr.1 choice when doing ordinary databases when web services is not implemented, thats true.And its rather strange that you all say that i shouldnt use GUID:s as primary keys. If you take alook at the "ASPNETDB" database that gets autogenerated by the "Visual Web Developer 2005 Express" when you do logins, alot of those tables has GUID primary keys. Isnt that rather odd?. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-30 : 15:30:53
|
quote:
Originally posted by rockmoose
Well the answer is of course to reselect the GUID of the newly inserted rowby it's natural key, and return it to the client.Billy the Guid
The table must have at least one key with business meaning.Do the reselect with a combination of columns that have a unique business meaning, and retrieve your guid.You do have some column(s) that make the record's unique (apart from the guid) don't You ?!?!?That "natural" key can be physically implemented as a PK, UIX or UC.quote:
If im using natural keys as my primary keys, im making the info that im about to store in the database part of the database structure, i think that the info that is stored in the database should be completely separated from the database structure, no exception.
And some people might think I'm religious about the natural / surrogate key issue !!!-- to sum it up it depends -- |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-08-30 : 15:36:02
|
quote:
....i think that the info that is stored in the database should be completely separated from the database structure, no exception.
What does this mean? If I want to store information about ducks I have to model for pigs?DavidMA front-end is something that tries to violate a back-end. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-30 : 15:55:30
|
>>-- to sum it up it depends --Here! Here!From an application perspective, the guid solves your multi-level hierarchy in a single post beatifully. And if you've only got 10s of thousands of records, no problem. However if you have 10s of millions of records you may regret using guids primary keys and choose make some compromises on the application side to maintain an acceptable level of database performance.natas, aren't we saying the same thing? That relying on client generated identity values won't work?quote:
I think its a bad application design that relies on client generated identity values surviving the posting process.
quote:
The client inserts a row in the parent and then a row in the child, but he havent saved it back to the database yet, and remember 100 ppl are doing this at once. Please explain how you would solve this issue.
Be One with the OptimizerTG |
|
|
|
Next Page
|
retrieving the IDENTITY from GUID PRIMARY KEYS