5

I have been using this for a long long time, but I couldn't find clear explanation on when it is not guaranteed unique. Here are my comments, please correct as you see fit.

Value is guaranteed to be unique(ly created) on the machine it is created. So GUIDs generated by .net app on a machine will never conflict with those that are created by SQL Server on the same machine.

In all other cases there is no guarantee. Theoretically it is always possible that if there are multiple creation sources(say applications on different computers) for the unique identifier field in a db on a different computer, there can be duplicates that'll be inserted.

EDIT: Apologies for not forming the question properly. I guess I wanted to know the probability of conflicts when generated on the same machine vs different machines, if c#'s Guid.NewGuid(), SQL Server's newid(),newsequentialid() functions or any other functions by different applications are used. The 'already answered' link has one of the replies where it says he actually encountered a case where that happened. Makes you wonder how frequently that can happen.

If I have a table with a uniqueidentifier field as the pk, should I have to worry about checking for uniqueness each time I do an insert by creating a new value either by different apps or SQL Server's functions?

p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
Brian
  • 1,337
  • 5
  • 17
  • 34
  • 5
    You should read *GUID guide* series by Eric Lippert: http://blogs.msdn.com/b/ericlippert/archive/tags/guids/ – MarcinJuraszek Aug 27 '13 at 17:51
  • Or at least RFC - http://www.apps.ietf.org/rfc/rfc4122.html – Alexei Levenkov Aug 27 '13 at 17:52
  • 1
    I'm pretty sure you're not asking about the `uniqueidentifier` data type. You may be asking about the `newid` function, or perhaps the `newsequentialid` function. The answer is completely different for these two functions, and probably also different if you mean something else. –  Aug 27 '13 at 17:54

1 Answers1

19

I guess I wanted to know the probability of conflicts when generated on the same machine vs different machines, if c#'s Guid.NewGuid(), SQL Server's newid(), newsequentialid() functions or any other functions by different applications

That's an impossibly broad question. "any other function in a different application" is not something we can reason about.

Instead let's ask an answerable question and then answer it.

What are the different mechanisms for generating GUIDs?

Version one GUIDs combine the MAC address of the current machine, the current time, and a few more bits of implementation-specific origin. They are therefore unique in time and space.

Version three and five GUIDs use a crypto-strength hash of a unique string. Their probability of collision is based on the probability of a hash collision.

Version four GUIDs use a pseudorandom number generator. Their probability of collision is based on the probability of the PRNG generating a collision.

What is the probability of a version one GUID collision on two different machines, given the assumption that machines have unique MAC addresses?

Zero.

What is the probability of a version one GUID collision when running two virtual machines on the same physical machine and generating GUIDs on each?

High; if the GUIDs are generated in the same time slice then they have a high probability of colliding.

If it hurts when you do that, don't do it.

The rest of the GUID algorithms do not depend on details of the machine.

What is the probability of a version three or five GUID colliding with another, given that the source strings are unique?

The probability is roughly the same as the probability of a version four GUID collision, so let's consider that.

What is the probability of a version four GUID collision?

The probability that a given v4 GUID will collide with any v4 GUID in a set of n unique v4 GUIDs is n divided by 2122.

The probability that given a set of n v4 GUIDs will contain at least one collision is harder to express but it is extremely small as long as n is significantly smaller than 261.

David R Tribble
  • 11,918
  • 5
  • 42
  • 52
Eric Lippert
  • 647,829
  • 179
  • 1,238
  • 2,067
  • @OP: Per Stephen Cleary ([A Few Words on GUIDs](http://blog.stephencleary.com/2010/11/few-words-on-guids.html)), SQL sequential guids are not RFC compliant. Stephen states (in the "The Database Problem(s)" section) that they have an increased likelihood of collision with other (RFC Compliant) guids as a result. – Brian Aug 28 '13 at 13:09
  • 1
    I seem to remember [some guy](http://blogs.msdn.com/b/ericlippert/archive/2012/04/24/guid-guide-part-one.aspx) writing a series about GUIDs a while back. :) – MgSam Aug 29 '13 at 12:42
  • n²/2n¹²² should be a reasonable approximation for "the probability that given a set of n v4 GUIDs will contain at least one collision" when n is smaller than 2^61. – CodesInChaos Sep 17 '13 at 14:43
  • Is it actually `n / (2^122)`? Bear in mind it's a **pseudo**-random number generator. In other words: how random is the generator? – Charlieface May 03 '22 at 22:57
  • 1
    @Charlieface: The PRNG used for GUIDs is not specified to be crypto strength and in practice, some implementations have used non-crypto-strength PRNGs. So: not random enough! You are right to note that the bound I'm describing here assumes a good PRNG. – Eric Lippert May 09 '22 at 22:32