Sequential Guid Generator

Question

Is there any way to get the functionality of the Sql Server 2005+ Sequential Guid generator without inserting records to read it back on round trip or invoking a native win dll call? I saw someone answer with a way of using rpcrt4.dll but I'm not sure if that would be able to work from my hosted environment for production.

Edit: Working with @John Boker's answer I attempted to turn it into more of a GuidComb generator instead of being dependent on the last generated Guid other than starting over. That for the seed instead of starting with Guid.Empty that I use

public SequentialGuid()
{
    var tempGuid = Guid.NewGuid();
    var bytes = tempGuid.ToByteArray();
    var time = DateTime.Now;
    bytes[3] = (byte) time.Year;
    bytes[2] = (byte) time.Month;
    bytes[1] = (byte) time.Day;
    bytes[0] = (byte) time.Hour;
    bytes[5] = (byte) time.Minute;
    bytes[4] = (byte) time.Second;
    CurrentGuid = new Guid(bytes);
}

I based that off the comments on

// 3 - the least significant byte in Guid ByteArray 
        [for SQL Server ORDER BY clause]
// 10 - the most significant byte in Guid ByteArray 
        [for SQL Server ORDERY BY clause]
SqlOrderMap = new[] {3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10};

Does this look like the way I'd want to seed a guid with the DateTime or does it look like I should do it in reverse and work backwards from the end of the SqlOrderMap indexes? I'm not too concerned about their being a paging break anytime an initial guid would be created since it would only occur during application recycles.

Answer 1

You could just use the same Win32 API function that SQL Server uses:

UuidCreateSequential

and apply some bit-shifting to put the values into big-endian order.

And since you want it in C#:

private class NativeMethods
{
   [DllImport("rpcrt4.dll", SetLastError=true)]
   public static extern int UuidCreateSequential(out Guid guid);
}

public static Guid NewSequentialID()
{
   //Code is released into the public domain; no attribution required
   const int RPC_S_OK = 0;

   Guid guid;
   int result = NativeMethods.UuidCreateSequential(out guid);
   if (result != RPC_S_OK)
      return Guid.NewGuid();

   //Endian swap the UInt32, UInt16, and UInt16 into the big-endian order (RFC specified order) that SQL Server expects
   //See https://stackoverflow.com/a/47682820/12597
   //Short version: UuidCreateSequential writes out three numbers in litte, rather than big, endian order
   var s = guid.ToByteArray();
   var t = new byte[16];

   //Endian swap UInt32
   t[3] = s[0];
   t[2] = s[1];
   t[1] = s[2];
   t[0] = s[3];
   //Endian swap UInt16
   t[5] = s[4];
   t[4] = s[5];
   //Endian swap UInt16
   t[7] = s[6];
   t[6] = s[7];
   //The rest are already in the proper order
   t[8] = s[8];
   t[9] = s[9];
   t[10] = s[10];
   t[11] = s[11];
   t[12] = s[12];
   t[13] = s[13];
   t[14] = s[14];
   t[15] = s[15];

   return new Guid(t);
}

See also

• Is there a .NET equalent to SQL Servers newsequentialid()

---

Microsoft's UuidCreateSequential is just an implementation of a type 1 uuid from RFC 4122.

A uuid has three important parts:

• node: (6 bytes) - the computer's MAC address
• timestamp: (7 bytes) - number of 100 ns intervals since 00:00:00.00, 15 October 1582 (the date of Gregorian reform to the Christian calendar)
• clockSequenceNumber (2 bytes) - counter in case you generate a guid faster than 100ns, or you change your mac address

The basic algorithm is:

1. obtain a system-wide lock
2. read the last node, timestamp and clockSequenceNumber from persistent storage (registry/file)
3. get the current node (i.e. MAC address)
4. get the current timestamp
5. • a) if the saved state was not available or corrupted, or the mac address has changed, generate a random clockSequenceNumber
   • b) if the state was available, but the current timestamp is the same or older than the saved timestamp, increment the clockSequenceNumber
6. save node, timestamp and clockSequenceNumber back to persistent storage
7. release the global lock
8. format the guid structure according to the rfc

There is a 4-bit version number, and 2 bit variant that also need to be ANDed into the data:

guid = new Guid(
      timestamp & 0xFFFFFFFF,  //timestamp low
      (timestamp >> 32) & 0xFFFF, //timestamp mid
      ((timestamp >> 40) & 0x0FFF), | (1 << 12) //timestamp high and version (version 1)
      (clockSequenceNumber & 0x3F) | (0x80), //clock sequence number and reserved
      node[0], node[1], node[2], node[3], node[4], node[5], node[6]);

Note: Completely untested; i just eyeballed it from the RFC.

• the byte order might have to be changed (Here is byte order for sql server)
• you might want to create your own version, e.g. Version 6 (version 1-5 are defined). That way you're guaranteed to be universally unique