Sequential GUIDs

Asked 7/4, 2011 at 17:44 Answered 23/3, 2017 at 13:21

I hope someone can answer this question.

How does the UuidCreateSequential method in the rpcrt4.dll class use to seed it's guids?

I know this much: Microsoft changed the UuidCreate function so it no longer uses the machine's MAC address as part of the UUID. Since CoCreateGuid calls UuidCreate to get its GUID, its output also changed. If you still like the GUIDs to be generated in sequential order (helpful for keeping a related group of GUIDs together in the system registry), you can use the UuidCreateSequential function.

The reason behind the question is. If I use this function to generate sequential GUIDs in a web cluster, how can I ensure that the GUIDs are close to a range of GUIDs without the potential of the GUID being duplicated?

Uretic answered 7/4, 2011 at 17:44 Comment(2)

Possible duplicate of Is there a .NET equalent to SQL Servers newsequentialid() – Bonner 20/11, 2017 at 11:35

??? This is a 7 year old question. But the questions are different and the answers given here are much more indepth. – Uretic 21/11, 2017 at 4:51

The Win32 UuidCreateSequential creates a Version 1 uuid.

Here's some sample version 1 uuid's created on my computer using UuidCreateSequential:

GuidToString                            Raw bytes
======================================  =================================================
{1BE8D85D-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 5D 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D85E-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 5E 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D85F-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 5F 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D860-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 60 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D861-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 61 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D862-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 62 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D863-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 63 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D864-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 64 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{1BE8D865-63D1-11E1-80DB-B8AC6FBE26E1}  1B E8 D8 65 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1
{220FB46C-63D1-11E1-80DB-B8AC6FBE26E1}  22 0F B4 6C 63 D1 11 E1  80 DB  B8 AC 6F BE 26 E1

The first thing that's important to note that these uuid contain my machine's MAC address (B8AC6FBE26E1):

enter image description here

                        Node
======================= ============
1BE8D85D-63D1-11E1-80DB B8AC6FBE26E1
1BE8D85E-63D1-11E1-80DB B8AC6FBE26E1
1BE8D85F-63D1-11E1-80DB B8AC6FBE26E1
1BE8D860-63D1-11E1-80DB B8AC6FBE26E1
1BE8D861-63D1-11E1-80DB B8AC6FBE26E1
1BE8D862-63D1-11E1-80DB B8AC6FBE26E1
1BE8D863-63D1-11E1-80DB B8AC6FBE26E1
1BE8D864-63D1-11E1-80DB B8AC6FBE26E1
1BE8D865-63D1-11E1-80DB B8AC6FBE26E1
220FB46C-63D1-11E1-80DB B8AC6FBE26E1

So if you're hoping for different computers to generate guid's that are "close" to each other, you're going to be disappointed.

Let's look at the rest of the values.

Seven and a half bytes of the remaining 10 bytes are a timestamp; the number of 100ns intervals since 00:00:00 15 October 1582. Rearranging those timestamp bytes together:

Timestamp              Node
=============== ====== ============
1E163D11BE8D85D 1-80DB B8AC6FBE26E1
1E163D11BE8D85E 1-80DB B8AC6FBE26E1
1E163D11BE8D85F 1-80DB B8AC6FBE26E1
1E163D11BE8D860 1-80DB B8AC6FBE26E1
1E163D11BE8D861 1-80DB B8AC6FBE26E1
1E163D11BE8D862 1-80DB B8AC6FBE26E1
1E163D11BE8D863 1-80DB B8AC6FBE26E1
1E163D11BE8D864 1-80DB B8AC6FBE26E1
1E163D11BE8D865 1-80DB B8AC6FBE26E1
1E163D1220FB46C 1-80DB B8AC6FBE26E1

You can see that guid's created on the same machine by UuidCreateSequential will be together, as they are chronological.

The 1 you see is the version number, in this case meaning a time based uuid. There are 5 defined versions:

1: time based version (UuidCreateSequential)
2: DCE Security version, with embedded POSIX UIDs
3: Name-based version that uses MD5 hashing
4: Randomly or pseudo-randomly generated version (UuidCreate)
5: Name-based version that uses SHA-1 hashing
6: The version RFC 4122 forgot (unofficial)

Giving:

Timestamp       Version      Node
=============== ======= ==== ============
1E163D11BE8D85D 1       80DB B8AC6FBE26E1
1E163D11BE8D85E 1       80DB B8AC6FBE26E1
1E163D11BE8D85F 1       80DB B8AC6FBE26E1
1E163D11BE8D860 1       80DB B8AC6FBE26E1
1E163D11BE8D861 1       80DB B8AC6FBE26E1
1E163D11BE8D862 1       80DB B8AC6FBE26E1
1E163D11BE8D863 1       80DB B8AC6FBE26E1
1E163D11BE8D864 1       80DB B8AC6FBE26E1
1E163D11BE8D865 1       80DB B8AC6FBE26E1
1E163D1220FB46C 1       80DB B8AC6FBE26E1

The last word contains two things.

The lower 12 bits is the machine-specifc Clock Sequence number:

Timestamp       Version   Clock Sequence   Node
=============== ======= = ================ ============
1E163D11BE8D85D 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D85E 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D85F 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D860 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D861 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D862 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D863 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D864 1       8 0DB              B8AC6FBE26E1
1E163D11BE8D865 1       8 0DB              B8AC6FBE26E1
1E163D1220FB46C 1       8 0DB              B8AC6FBE26E1

This machine-wide persistent value is incremented if:

you switched network cards
you generated a UUID less than 100 ns from the last one (and the timestamp would collide)

So, again, any guid's created by UuidCreateSequential will (ideally) have the same Clock Sequence number, making them "near" to each other.

The final 2 bits, is called a Variant, and is always set to binary 10:

Timestamp       Version Variant Clock Sequence   Node
=============== ======= ======= ================ ============
1E163D11BE8D85D 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D85E 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D85F 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D860 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D861 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D862 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D863 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D864 1       8       0DB              B8AC6FBE26E1
1E163D11BE8D865 1       8       0DB              B8AC6FBE26E1
1E163D1220FB46C 1       8       0DB              B8AC6FBE26E1

So there you have it. Sequential guid's are sequential; and if you create them on the same machine they will be "near" to each other in a database.

But you want to know what actually happens with two sequential UUID's created on different computers.

Using our newfound knowledge of Version 1 guids, let's construct two guid's for the same timestamp from different machines, e.g.:

{1BE8D85D-63D1-11E1-80DB-B8AC6FBE26E1}
{1BE8D85D-63D1-11E1-80DB-123456789ABC}

First let's insert a bunch of guid's with sequential timestamps. First create a temporary table to store our guid's in, and cluster by the guid:

--DROP table #uuidOrderingTest
CREATE TABLE #uuidOrderingTest
( 
    uuid uniqueidentifier not null
)

CREATE clustered index IX_uuidorderingTest_uuid ON #uuidOrderingTest 
( 
   uuid
)

Now insert the data:

INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D866-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D862-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D861-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D85E-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D864-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D863-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D85F-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D85D-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D865-63D1-11E1-80DB-B8AC6FBE26E1}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D860-63D1-11E1-80DB-B8AC6FBE26E1}')

Note: i insert them in random timestamp order, to illustrate that SQL Server will cluster them.

Get the rows back and see what order they're in sequential (timestamp) order:

SELECT * FROM #uuidOrderingTest

uuid
------------------------------------
1BE8D85D-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D85E-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D85F-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D860-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D861-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D862-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D863-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D864-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D865-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D866-63D1-11E1-80DB-B8AC6FBE26E1

Now lets insert guid's with:

the same timestamps
but different node (i.e. MAC address):

Insert the new guids from a "different" computer:

INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D866-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D862-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D861-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D85E-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D864-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D863-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D85F-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D85D-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D865-63D1-11E1-80DB-123456789ABC}')
INSERT INTO #uuidOrderingTest (uuid) VALUES ('{1BE8D860-63D1-11E1-80DB-123456789ABC}')

And get the results:

uuid
------------------------------------
1BE8D85D-63D1-11E1-80DB-123456789ABC
1BE8D85E-63D1-11E1-80DB-123456789ABC
1BE8D85F-63D1-11E1-80DB-123456789ABC
1BE8D860-63D1-11E1-80DB-123456789ABC
1BE8D861-63D1-11E1-80DB-123456789ABC
1BE8D862-63D1-11E1-80DB-123456789ABC
1BE8D863-63D1-11E1-80DB-123456789ABC
1BE8D864-63D1-11E1-80DB-123456789ABC
1BE8D865-63D1-11E1-80DB-123456789ABC
1BE8D866-63D1-11E1-80DB-123456789ABC
1BE8D85D-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D85E-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D85F-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D860-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D861-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D862-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D863-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D864-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D865-63D1-11E1-80DB-B8AC6FBE26E1
1BE8D866-63D1-11E1-80DB-B8AC6FBE26E1

So there you have it. SQL Server order's Node before Timestamp. Uuid created from different machines will not be clustered together. Would have been better if it hadn't done so, but whatcha gonna do.

Nez answered 1/3, 2012 at 20:6 Comment(2)

Does Zalgo issue your DHCP addresses? – Garvey 24/2, 2017 at 19:43

@JamesMcLachlan My Windows is configured for a locale different from en-us. You're seeing a date formatted in the non US-English locale. – Nez 25/2, 2017 at 3:39

Rather than rely on the Win32 API, I typically use my own variant of a sequential guid which replaces eight bytes of the a standard guid with ticks from a datetime.

var guidBinary = new byte[16];
Array.Copy( Guid.NewGuid().ToByteArray(), 0, guidBinary, 0, 8 );
Array.Copy( BitConverter.GetBytes( DateTime.Now.Ticks ), 0, guidBinary, 8, 8 );
return new Guid( guidBinary );

Lulualaba answered 7/4, 2011 at 18:8 Comment(5)

So in the OP's case, they could make the 8 bytes sequential? – Rahr 7/4, 2011 at 18:10

@Robert Harvey - Or six or four. Depends on what balance of unique-ness to sequential-ness they want. – Lulualaba 7/4, 2011 at 18:13

@Robert Harvey - (Obviously, to get something other than the eight I used here, they'd have to tweak the routine.) – Lulualaba 7/4, 2011 at 18:15

This does not produce GUIDs sequential on SQL Server 2017. – Stockpile 20/7, 2021 at 17:37

@Stockpile - Yes it does. Microsoft didn't change how binary was interpreted from one version of SQL Server to another. – Lulualaba 1/8, 2021 at 23:44

Not sure about the Win32 way, but you could use the 'undocumented' 'newSequentialID()' in MSSQL, if you have MSSQL Database connectivity.

I Say 'undocumented' because it's treated as incorrect when trying to save it as the default value for a MSSQL Identity column, and you have to override it and say you want to use it.

Thoer answered 7/4, 2011 at 17:49 Comment(4)

well, i know about newsequentialId() in SQL. But, we're trying to view the cons/pros of using c# to create sequential guids vs. SQL Server... so, if we have a cluster web server and we use code to generate the GUIDs how does it effect it when there are multiple servers doing the same thing and how do we keep the GUIDs closely sequential without duplicating? – Uretic 7/4, 2011 at 18:0

In addition, you cannot use the NewSequentialGuid() function outside of a default constraint. – Lulualaba 7/4, 2011 at 18:6

@Thomas: That is known; you'd use a table to create sequential id's that don't duplicate. – Thoer 7/4, 2011 at 18:7

sequential guids as ID's are faster on inserts as you add data at the end of the index and not at some random page, this becomes important when you have larger tables – Turntable 24/1, 2020 at 14:15

You could have a central table that has the last known starting UID for a range and increment it.

eg: DB1 creates a GUID {AA333F14-FCCD-4bee-9F8F-9D9BDF1B8766} and writes this to the table. DB2 comes online and sees {AA333F14-FCCD-4bee-9F8F-9D9BDF1B8766} and increments it by some set number, like 1,000,000,000,000,000 or something really high so you won't have any overlapping values.

But really, GUIDs are almost useless when used incrementally.

I guess the really question is, what are you using GUIDs for? If you want an incrmenting number, just use a 64bit int(aka, bigint)

Purser answered 7/4, 2011 at 18:36 Comment(0)

I changed Thomas, answer to have first 8 bytes as incremental

 var guidBinary = new byte[16];
 Array.Copy(BitConverter.GetBytes(DateTime.Now.Ticks), 0, guidBinary, 0, 8);
 Array.Copy(Guid.NewGuid().ToByteArray(), 8, guidBinary, 8, 8);
 return new Guid(guidBinary);

and result will be something like

b0c99468-714a-08d4-88bd-39e0b53455fb
b122b4b8-714a-08d4-9b12-924e850ad2fe
b1254cf0-714a-08d4-b7c9-954d36290ce5
b12573ff-714a-08d4-b000-632c3a58874d

Observable answered 23/3, 2017 at 13:21 Comment(1)

This does not produce GUIDs sequential on SQL Server 2017 (tested on an Azure SQL Database with the compatibility level set to 140). – Stockpile 20/7, 2021 at 17:40

Recommended topics

Hot tags