7

As per microsoft documentation on UniqueIdentifier, This value is always a unique globally beacuse it's based on network clock and CPU clock time and on the other hand the same documentation says

uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column.

I'm not able to come to a conclusion how UniqueIdentifier (GUIDs) can be unique globally, as Network address (Mac address) can be same on two different networks, How GUIDs can be unique globally with which combinations and also why Microsoft says there should be primary or unique constraint in order to ensure we've unique UniqueIdeitifer value always.

https://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx

  • 4
    Because even if a procedure that generates these GUIDs is perfect and never creates any duplicates, still nothing forbids you from entering a single value that this procedure yielded into many rows. The only thing that can prevent it, is a UNIQUE constraint. – ypercubeᵀᴹ Oct 16 '16 at 14:54
11

Problem #1

As per Microsoft documentation on UniqueIdentifier, This value is always a unique globally because it's based on network clock and CPU clock time... (emphasis added)

The main problem here is that you are confusing two different things as being two terms that refer to one thing: UNIQUEIDENTIFIER and GUIDs.

  • UNIQUEIDENTIFIER is a datatype. Datatypes define the nature of the data that they (i.e. columns and variables of this type) can contain (e.g. min / max values, etc) and certain behaviors of the data (e.g. how to handle comparisons). This particular datatype merely holds GUID / UUID values. But it is not data, so the concept of uniqueness does not apply to it. And the word "Unique" in the name "UniqueIdentifier" is not a promise, or even statement, regarding actual uniqueness.

  • GUIDs / UUIDs are actual values that can be stored as UNIQUEIDENTIFIER, but could also be stored as VARBINARY / BINARY, (N)VARCHAR / (N)CHAR, and maybe some others. While the UNIQUEIDENTIFIER datatype is the best choice (in SQL Server) for storing these values, storing the values in the other types does not make the values any more or less unique.

Problem #2

I'm not able to come to a conclusion how UniqueIdentifier (GUIDs) can be unique globally, as Network address (Mac address) can be same on two different networks

The second problem here is that you are accepting, as fact, a technical error in the documentation that you linked to. I assume you are referring to this statement:

A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value.

That statement is referring to functions like NEWID() in T-SQL and Guid.NewGuid() in .NET that create new GUID / UUID values, and the intention of them to always generate unique values. However, that is not reality: newly generated GUIDs are not guaranteed to be unique. As you already pointed out, MAC Addresses aren't necessarily unique (they can even be spoofed; more info in the "Related info" section below). Also, from other Microsoft documentation:

  • MSDN page for .NET Guid Structure states (emphasis added):

    A GUID is a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required. Such an identifier has a very low probability of being duplicated.

  • The .NET Guid.NewGuid() method (which is used to generate new GUID / UUID values) calls Win32Native.CoCreateGuid. The documentation for that function states (emphasis added):

    To a very high degree of certainty, this function returns a unique value – no other invocation, on the same or any other system (networked or not), should return the same value.

Please note that the non-SQL Server documentation doesn't even mention MAC Address. And the documentation for CoCreateGuid points to the real function that does the generation: UuidCreate. The documentation for that function states:

For security reasons, it is often desirable to keep ethernet addresses on networks from becoming available outside a company or organization. The UuidCreate function generates a UUID that cannot be traced to the ethernet address of the computer on which it was generated. It also cannot be associated with other UUIDs created on the same computer. If you do not need this level of security, your application can use the UuidCreateSequential function, which behaves exactly as the UuidCreate function does on all other versions of the operating system.

The implication here is that MAC Address is specifically not used (unless using NEWSEQUENTIALID()). And in fact, generating a few GUIDs in SQL Server via NEWID() indicates that they are RFC 4122, Version 4 UUIDs, which are extremely likely to be unique. There is a chart here, Random UUID probability of duplicates, that shows just how unlikely it is to have duplicates. However, even a very, very low probability of duplicates is not a guarantee of uniqueness.

And so...

There is no guarantee that newly generated GUID / UUID values are unique. And, even if there was a guarantee, the UNIQUEIDENTIFIER datatype would still have nothing to do with actual uniqueness (as is shown in Brent's answer). Uniqueness, for one or more columns (i.e. data, not datatypes) can only be enforced by Unique Indexes / Constraints.


Related info:

  • This text about the network card exists in the SQL Server 7.0 documentation too. I'm not sure that more recent implementations of CoCreateGuid even use the mac address? I thought they used something like en.wikipedia.org/wiki/… - though NEWSEQUENTIALID does. – Martin Smith Oct 17 '16 at 8:05
  • 1
    @MartinSmith With so much documentation it's no surprise that some items don't get updated, especially when the change is related functionality that exists outside of SQL Server. Thanks for bringing this up. I just updated my answer with more details. The point still stands, though, that even when generating version 4 (random) UUIDs, lack of collisions is a matter of probability, not a guarantee. And I am making this point to counter the O.P.'s misunderstanding that Microsoft somehow could (regardless of mechanism) generate GUIDs that are guaranteed to be unique. – Solomon Rutzky Oct 17 '16 at 17:06
7

Because a UNIQUEIDENTIFIER column can store whatever UNIQUEIDENTIFIER you put in it. Take this code:

CREATE TABLE dbo.Test (MyID UNIQUEIDENTIFIER);
INSERT INTO dbo.Test VALUES ('809A6AA3-E6D7-4099-8D6C-6B21ED732013');
INSERT INTO dbo.Test VALUES ('809A6AA3-E6D7-4099-8D6C-6B21ED732013');
INSERT INTO dbo.Test VALUES ('809A6AA3-E6D7-4099-8D6C-6B21ED732013');
SELECT * FROM dbo.Test;

There's nothing stopping you from doing that - unless you specify something about the table that enforces uniqueness.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.