How SQL Server creates uniqueidentifier using NEWID()

Question

I have a user_info table in five different locations. Now I need to integrate all the rows into a central user_info table. To do this I need a unique id for each row in source tables. Because when all the rows come into the central table, then each user must have a unique ID.

Now my questions are:

if I use uniqueidentifier NEWID() for each source table then is it will be unique for globally & life time or have any chance to be duplicate?
How does SQL Server create the NEWID()? I need to know the key generation structure.

Mykola · Accepted Answer · 2016-01-27 14:49:25Z

8

Yes, there is no chance of a duplicate between machines.

NEWID() is based on a combination of a pseudo random number (from the clock) and the MAC address of the primary NIC.

However, inserting random numbers like this as the clustered key on a table is terrible for performance. You should consider either NEWSEQUENTIALID() or a COMB-type function for generating GUIDs that still offer the collision-avoidance benefits of NEWID() while still maintaining acceptable INSERT performance.

edited Jan 27, 2016 at 14:49

Mykola

3,4136 gold badges27 silver badges40 bronze badges

answered Sep 23, 2012 at 6:59

richardtallent

35.5k14 gold badges87 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martin Smith Over a year ago

Any documentation about what windows API call SQL Server uses for NEWID? I know that NEWSEQUENTIALID uses the MAC address as this is clearly visible in the GUID but couldn't see any documentation for NEWID.

Seph Over a year ago

CoCreateGuid most likely? Eric Lippert had a [blog post series][1] about GUIDs in general. See also: stackoverflow.com/questions/39771/… For NEWID() the first hex digit of the third section is always 4 (when I try it), for NEWSEQUENTIALID() it uses the MAC address mentioned above. I can't remember what happens when there is no MAC address in the computer - anyone know what happens in SQL in this case? [1]: blogs.msdn.com/b/ericlippert/archive/2012/05/07/…

jumxozizi Over a year ago

How accurate time does newid() use? It sounds like if I fetch two ids at the same time I should get a duplicate, yet I've never seen it happening.

richardtallent Over a year ago

@tubelius, the newid() value is based on a pseudorandom number generated from the clock and other factors. It is not the clock value itself, so the chances of duplication are so infinitesimal that it isn't even worth worrying about. If you want the timestamp to be explicitly built into the value (to make it sort roughly sequentially without the disadvantages of newsequentiaid()), you'll need a COMB library. I have an open-source one (RT.Comb) on Github / Nuget.

kelalaka Over a year ago

@richardtallent No, you should mention that there is a negligible change. Saying there is no change means that there is zero probability, which is obvious not!

Collectives™ on Stack Overflow

How SQL Server creates uniqueidentifier using NEWID()

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related