[ACCEPTED]-Sequential GUIDs-guid

The Win32 UuidCreateSequential creates a Version 1 uuid.

Here's some sample 44 version 1 uuid's created on my computer 43 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 42 to note that these uuid contain my machine's 41 MAC address (B8AC6FBE26E1):

                        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 40 computers to generate guid's that are "close" to 39 each other, you're going to be disappointed.

Let's 38 look at the rest of the values.

Seven and 37 a half bytes of the remaining 10 bytes are 36 a timestamp; the number of 100ns intervals since 35 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 34 can see that guid's created on the same 33 machine by UuidCreateSequential will be together, as they are 32 chronological.

The 1 you see is the version number, in this 31 case meaning a time based uuid. There are 5 defined 30 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 29 things.

The lower 12 bits is the machine-specifc 28 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 27 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 26 created by UuidCreateSequential will (ideally) have the same 25 Clock Sequence number, making them "near" to 24 each other.

The final 2 bits, is called a 23 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 22 you have it. Sequential guid's are sequential; and 21 if you create them on the same machine they will be "near" to 20 each other in a database.

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

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

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

First let's 14 insert a bunch of guid's with sequential 13 timestamps. First create a temporary table 12 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 11 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 10 timestamp order, to illustrate that SQL 9 Server will cluster them.

Get the rows back 8 and see what order they're in sequential 7 (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 6 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 5 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 4 Server order's Node before Timestamp. Uuid created from 3 different machines will not be clustered 2 together. Would have been better if it hadn't 1 done so, but whatcha gonna do.

Rather than rely on the Win32 API, I typically 3 use my own variant of a sequential guid 2 which replaces eight bytes of the a standard 1 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 );

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

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

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

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

But really, GUIDs 4 are almost useless when used incrementally.

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

I changed Thomas, answer to have first 8 2 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 1 like

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