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.

Edit: 2020+ update

At this point I strongly prefer Snowflake identifiers using something like https://github.com/RobThree/IdGen

user_1157051 · Answer

Maybe interesting to compare with the other suggestions:

EntityFramework Core also implements a sequentialGuidValueGenerator. They generate randoms guids for each value and only change the most significant bytes based on a timestamp and thread-safe increments for sorting in SQL Server.

source link

This leads to values that are all very different but with a timestamp sortable.

user_37055 · Answer

Not specifically guid but I now normally use a Snowflake style sequential id generator. The same benefits of a guid while having even better clustered index compatibility than a sequential guid.

Flakey for .NET Core

IdGen for .NET Framework

user_9945857 · Answer

I ended up writing this C# class to achieve the following that I needed with SQLite (in which GUIDs are stored as BLOBs and sort order is determined by memcmp). I suppose it is not a real GUID but it's tested and it does it job on SQLite.

Sortable (memcmp) sequential 16-byte array
Using counter to guarantee correct ordering
Thread safe

The time resolution seems to vary on OSes and on my Windows it's worse than 1ms. Therefore I chose to use a second bsed resolution where the first 34 bits represent the UnixTime (UTC), and then there is a 22 bit thread safe counter which increments for every request on the same second. If the counter reaches its max, the function sleeps for 500ms and tries again.

On my laptop I could generate and store ~3,2M 16 byte arrays per second.

The class returns a 16-byte array, not a GUID.

namespace SeqGuid
{
public static class SeqGuid
{
    static private Object _lock = new Object();
    static Random _rnd = new Random();
    static UInt64 _lastSecond = 0;
    static int _counter = 0;

    public static UInt64 UnixTime()
    {
        return (UInt64)DateTime.UtcNow.Subtract(DateTime.UnixEpoch).TotalSeconds;
    }

    public static byte[] CreateGuid()
    {
        // One year is 3600*24*365.25 = 31557600 seconds
        // With 34 bits we can hold ~544 years since 1970-01-01
        // 

        UInt64 seconds = UnixTime();

        lock (_lock)
        {
            if (seconds == _lastSecond)
            {
                // 22 bits counter, aka 11-1111-1111-1111-1111-1111 / 0x3F FFFF; 4.1M max / second, 1/4 ns
                _counter++;
                if (_counter >= 0x3F_FFFF)
                {
                    Thread.Sleep(500);
                    // http://msdn.microsoft.com/en-us/library/c5kehkcz.aspx
                    // A lock knows which thread locked it. If the same thread comes again it just increments a counter and does not block.
                    return CreateGuid();
                }
            }
            else
            {
                _lastSecond = seconds;
                _counter = 0;
            }
        }

        // Create 56 bits (7 bytes) {seconds (34bit), _counter(22bit)}
        UInt64 secondsctr = (seconds << 22) | (UInt64)_counter;

        byte[] byte16 = new byte[16] {
            (byte) ((secondsctr  >> 48) & 0xFF),
            (byte) ((secondsctr  >> 40) & 0xFF),
            (byte) ((secondsctr  >> 32) & 0xFF),
            (byte) ((secondsctr  >> 24) & 0xFF),
            (byte) ((secondsctr  >> 16) & 0xFF),
            (byte) ((secondsctr  >>  8) & 0xFF),
            (byte) ((secondsctr  >>  0) & 0xFF),
            (byte) _rnd.Next(0,255),
            (byte) _rnd.Next(0,255), (byte) _rnd.Next(0,255),
            (byte) _rnd.Next(0,255), (byte) _rnd.Next(0,255),
            (byte) _rnd.Next(0,255), (byte) _rnd.Next(0,255),
            (byte) _rnd.Next(0,255), (byte) _rnd.Next(0,255)};

        return byte16;
    }
}
}

Sequential Guid Generator

12 Answers