Sql-server – How Uniqueidentifier in SQL Server is always a unique value globally

sql serversql-server-2012uniqueidentifieruuid

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

Best Answer

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:

Related Solutions

Sql-server – Securely generate a UNIQUEIDENTIFIER in SQL Server

SELECT CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER)

Should do the trick I would have thought.

CRYPT_GEN_RANDOM

Returns a cryptographic random number generated by the Crypto API (CAPI).

Sql-server – Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

My suggestion would be:

(a) talk to Azure support. This is not how it should be working AFAIK.

(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:

SELECT name, etc.
 FROM sys.indexes AS i
 INNER JOIN sys.dm_db_index_physical_stats AS s
 ON ...
 WHERE ... criteria ...
 AND NOT EXISTS 
 (
   SELECT 1 FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
     ON ic.object_id = c.object_id
     AND ic.column_id = c.column_id
     AND ic.key_ordinal = 1 -- only leading column
     AND c.system_type_id = 36 -- uniqueidentifer
     WHERE ic.index_id = i.index_id
 );

(c) before deciding to manually deal with these columns, wait until they actually cause you a performance problem that you can demonstrate and undeniably identify the fragmentation of this index as the problem.

(d) or, just use the inverse query in (b) to perform manual drop/create all the time instead of dealing with the problem over and over.