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

Martin Wang 101 Reputation points
2021-02-23T02:52:57.917+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,704 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.1K Reputation points
    2021-02-23T10:40:20.927+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Cris Zhan-MSFT 6,601 Reputation points
    2021-02-24T06:00:52.037+00:00

    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?