| Author |
Topic |
|
vermorel
Starting Member
26 Posts |
 Posted - 2006-05-08 : 04:47:55
|
| Let's say that I have a database with 2 tables named 'TableA' and 'TableB'. Each one of those two tables contains the two fields 'MyKey' and 'MyData'. The field 'MyKey' has the type 'uniqueidentifier' and its default value is 'getnewid()'.Now I would like to consider a third table named 'TableC' with two fields 'KeyRef' and 'MetaData'. The type of 'KeyRef' is 'uniqueidenfier' and corresponds to an element of either 'TableA' or 'TableB'.If the GUID identifiers are trully unique then there is no issue to proceed that way (I am not consering perf matters here). On the contrary, if GUID are not unique, then there is a collision possibility.My question is simply: can I consider the GUID to be unique in MS SQL Server (2000 or 2005).Thanks in advance,Joannès |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-08 : 06:16:07
|
| GUIDs are almost unique. It's very unlikely that you will ever get a duplicate.If you do then it should be taken care of by your unique index and will happen seldom enough to just let it fail.I'm trying to find an article which gives the chances of a duplicate but can't find any.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-05-08 : 06:17:48
|
| Yeah, you can consider the GUID to be unique. BOL says:"Values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world."Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-05-08 : 06:22:45
|
It's not an article, but I found this (at the bottom of this thread: http://www.mcse.ms/archive94-2004-9-1105764.html)..."You must generate something like 26,087,347,097,389 random 16-byte values before the probability of a duplicate reaches even 1 in 1,000,000,000,000"I'm willing to believe it, and that's "unique enough" for me.  Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-08 : 07:54:46
|
| But"Values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world."isn't true.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-08 : 09:38:53
|
| No, but they are unique across a single platform. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-08 : 11:29:46
|
I believe that"Values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world."used to be true when they were based on the NIC of the machine - but that was regarded as an intrusion of privacy so they are no longer based on that  Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-05-08 : 12:20:44
|
| I'm with Kristen on this. If "back in the day" they wer based on the MAC address of the NIC and were "unique" to a machine, how unique are they now that they are not based on MAC adddress??Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-08 : 12:24:44
|
| I reckon its only likely to be a problem if the GUIDs are generated on the Client, and not on the Server.However, we've decided to ignore this issue, but we do put a UNIQUE INDEX on GUID columns, and if the software ever fails the user will no doubt press RETRY and all will be well!Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-08 : 13:57:15
|
| Any number higher than the amount of money that Bill Gates owns is really big and 26,087,347,097,389 qualifies. However, as big as it appears to be, America's national debt is rapidly approaching that number which is actually kind of sobering. On a serious note I to was under the impression that there were guaranteed unique, hence their name. "Hopefully Unique" "Unique Most of the time" doesn't have quite the same ring to it though. Kristen - Good idea on the Unique index that will resolve the issue.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-08 : 14:13:13
|
OK, lets make sure I've got this right:Bill Gates is single-handled responsible for America's national debt?  Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-08 : 14:31:14
|
| Don't be silly ... We are in debt because we've bred idiots in our country for so long, that they don't even understand the concept of debt any longer. 1. We have people who whine/cry about the price of gas (a whopping $3.00 per gallon .. I know cry me a river compared to the price that most in Europe pay), and think it is a good idea when one of our politicians says "We'll solve this by sending $100 to every family in America." They simply aren't smart enough to think "Hey where is that $100 coming from that you are going to send me?" 2. We are in debt because we spend our tax dollars to care for the health and human services to cover 11-20 million people who've invaded our country illegally. And then we say "Hey maybe we should make them US citizens because it would cost too much to put them on a bus back home. 3. We are in debt as a nation because we have a nation of people who are themselves in debt and have gone into that debt purchasing products from countries like China and Mexico. Then the governments spends money it doesn't have to send for programs that could pretty accurately be called "vote getting programs."But hey the whole world loves America. It's not like any of these foreign countries and foreign banks that are lending us money would ever call in that debt and try to intentionally destroy us ... right? So hey, let's eat drink and be marry and keep spending money that isn't ours. This is America after all. And while we are at it let's borrow some more money to buy pc's and purchase those freakin Microsoft Software Licenses. Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-08 : 14:56:09
|
I don't consider uniqueidentifiers as good candidates for keys anyway,unless it is a distributed system where keys are generated in more than one location.I wouldn't worry about the possibility for collissions though, unless I was designing a mars lander or something.In which case natural keys would be guaranteed to be unique.Funny, when the guid was generated from the MAC Adress, it was the "natural" key of the MAC Adress that guaranteed uniqueness.Nice rant Dalton  If gasoline is so cheap in the US, I wonder how much  I can get for my $2.000 creditcard credit...  rockmoose |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-08 : 15:19:06
|
| Well in most college towns you'd be able to get 8 .25 draft beers during happy hour. I hadn't thought about it before but drunk driving is becoming more and more of a problem. Perhaps the recent hike in prices is really intended to curb that. Hard to drink and drive, when you can only afford to do 1 or the other. I wonder what tabloid magazine would publish that conspiracy theory?Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-08 : 15:54:03
|
| GUID collisions are one of those things that you can worry about if you want, but the chances are so small of it happening, that it really isn’t worth worrying about compared to all the other things that can go wrong in a database.Kind of like worrying about getting hit by a meteor, but ignoring the danger of getting hit by a car, falling down the stairs, or being shot by an angry wife.CODO ERGO SUM |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-08 : 16:06:20
|
| With those prices, I can afford to drink and take a cab The oil business is one big conspiracy all in itself.I think the correlation is a neglect of public transportation services, so that people just say fuck it and take the car.Maybe you guys need to borrow some more money to get public transportation to work.rockmoose |
|
|
|
|
Are GUID trully unique?