question

MartinWang-5107 avatar image
0 Votes"
MartinWang-5107 asked ·

SELECT NEWID(): what is the mechanism to ensure it is unique

hi all I'd like to ask what is the mechanism to ensure it is unique for SELECT NEWID(). I am using C# DAPPER to call SELECT NEWID() to generate unqiue Id, since my C# program is multi-thread, so maybe it is possible that in the same time(let's imagine the same millisecond) to call SELECT NEWID() and generate id, in this case, can I make sure it is unique? I'd like to know how sql server generate this unique ID? Thank you very much

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered ·

According to sample values returned by NEWID, it seems that SQL Server uses Version 4, which is based on random numbers. (See, for example, https://en.wikipedia.org/wiki/Universally_unique_identifier).

If this is true, then theoretically it is possible to get collisions on same of different servers, however “probability so small that it can normally be ignored”, “the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion” (https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_(random)).

Regarding other collisions caused by concurrent execution, I think that they are not possible. Documentation does not mention this, maybe because the whole SQL Server is designed for concurrent access.




· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered ·

Hi,

The NEWID() function can creates a unique value of type uniqueidentifier. It generates a unique value whenever you execute it. The value it produces is a randomly generated 16-byte GUID (Globally Unique IDentifier). This is also known as a UUID (Universally Unique IDentifier).

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.
https://dba.stackexchange.com/questions/152414/how-uniqueidentifier-in-sql-server-is-always-a-unique-value-globally

Also take a look at the following posts.
SQL Server : does NEWID() always gives a unique ID?


· 1 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MartinWang-5107,
Is there any update on this case?

0 Votes 0 ·