Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: uniqueness of GUIDs of different databases

    I just had a discussion about the uniqueness of GUID's worldwide? Does anyone know the answer to these questions?

    Will (can) one and the same database system (like one instance of SQL Server 2008 R2) generate duplicte GUID's?

    Will (can) different database systems (like a mixture of SQL Servers, DB2's, Oracles, ... different versions) generate duplicate GUID's?

    What is best practice to generate unique GUID's?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A GUID is a 16 byte identifier. This gives it 2^128 possible values. Since this is still a finite value, yes, a system can generate duplicate GUIDs. I can also try to win every lottery worldwide for a year, and have only slightly worse odds.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I agree with that.

    The discussion was that we should find a waterproof GUID generator/algorithm and let that generate GUIDs for us, instead of letting the database do it for us. I assume MS has already done that for us and has incorporated it in MSSQL.

    Apart from
    Code:
    MyGUID	UNIQUEIDENTIFIER	NOT NULL	DEFAULT NewId()
    and defining a unique constraint on it, like a PK, is there anything else we should do?

    MS is a bit ambiguous about all this:
    A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value.
    uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column.
    So any GUID is truly unique in the world, except on the computer that generated that GUID, that computer can generate it over and over again. Weird definition of uniqueness.
    Last edited by Wim; 08-18-11 at 18:37. Reason: corrected typo
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, the two items make perfect sense. UniqueIdentifier is a datatype, just like int. You can have an identity column with unique ids in it, and a child table that has repeats of those same IDs. The second excerpt is just to clarify that although the datatype is "uniqueidentifier, you can put duplicates in the same column (barring an outside force like a PK or Unique constraint).

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Will?
    yes
    inevitably
    not later than the 2^128th GUID has been generated (a bit sooner in fact because most GUIDs have a fixed portion defining what type of GUID it is)

    don't forget the birthday paradox (it goes something like there is a 50% probability of two people in a random sample of 25 people sharing the same month/day birthdate) that suggests that you certainly wont need to wait for anything near 2^128 GUIDs to be generated before there is a duplicate.

    Likely?
    not really
    these numbers are impressively huge:
    2^128 is around 3 x 10^38 (38 is too many zeros for me to type).

    should you worry about a GUID collision?
    i guess you should worry more about being hit by lightening on the way to collect your lottery jackpot win.
    ...but a duplicate GUID is inevitable (unlike your lottery win) when enough GUIDs have been generated. will the duplicates collide?
    to collide they have to meet (same db, same corporation, same machine or similar encounter) which is somewhat less likely that duplicate GUIDs existing anywhere on the planet.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    One little gotchya. If you do not have a unique constraint, you can have duplicate values in an uniqueidentifier column, and I saw this happen in a SQL 2k database a number of years ago and it caused great consternation for everyone involved. Don't believe me if you wish.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See 2.5.5.1 Uniqueness for the details, but essentially if you generate/try 1024 GUIDs per second on one billion servers, you could theoretically hit a duplicate GUID before entropy claims Sol 10 to the 16th power years). Using Microsoft generated GUID values, all one billion servers would need to be on different Active Directory domains and have different MAC addresses.

    Short answer, don't loose sleep on this!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you all for your answers. They are in line with my standpoint.
    Short answer, don't loose sleep on this!
    I won't
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by Pat Phelan View Post
    (...) all one billion servers would need to be on different Active Directory domains and have different MAC addresses.
    And here is lies the issue: Virtualization. I've seen quite a few environments with duplicate MAC addresses given to virtual servers. This is easily detected within a network segment, but if the two servers are located in different segments, eg different data centeres, it is usually NOT detected.

    As of today, GUID algorithm V4 (peseudo random number) is used, not the old V1 (MAC Address). Still, Microsoft claims that computers without network card will be able to generate the same GUIDs, so the pseudo random algorithm most likely uses MAC address (and something else?) to seed the random algorithm (thus pseudo random).

    In other words, for physical NICs, you'll most likely be fine. If you have virtual machines, I would be a bit more careful.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you've got things backwards roac. The way I understand the algorithm, if two machines have the same primary MAC address on a given AD instance the chances of duplicate GUIDS are astronomically higher than if they have different GUIDS.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    I think that you've got things backwards roac. The way I understand the algorithm, if two machines have the same primary MAC address on a given AD instance the chances of duplicate GUIDS are astronomically higher than if they have different GUIDS.

    -PatP
    Is that a typo? Shouldn't that be "different MAC's". I knew about the MAC address, but I didn't knew the AD instance matters too.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, I meant different MAC addresses instead of different GUIDs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •