Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save danielmarschall/7fafd270a3bc107d38e8449ce7420c25 to your computer and use it in GitHub Desktop.
Save danielmarschall/7fafd270a3bc107d38e8449ce7420c25 to your computer and use it in GitHub Desktop.
SQL Server sortable UUID in C#
using System;
using System.Threading;
namespace GenerateSqlServerSortableTimeBasedGuid_App
{
internal class Program
{
static readonly Random random = new Random();
/**
* The sorting of SQL Server is rather confusing and incompatible with UUIDv6 and UUIDv7.
* Therefore this method generates UUID which are sortable by SQL Server.
* Version 1: Resolution of 1 milliseconds, random part of 16 bits, local timezone, 48 zero bits "signature", NOT UUIDv8 conform.
* Version 2: Resolution of 1 milliseconds, random part of 16 bits, UTC time, 48 bit random "signature", UUIDv8 conform.
* C# implementation: https://gist.github.com/danielmarschall/7fafd270a3bc107d38e8449ce7420c25
* PHP implementation: https://github.com/danielmarschall/uuid_mac_utils/blob/master/includes/uuid_utils.inc.php
*/
public static Guid GenerateSqlServerSortableTimeBasedGuid(int hickelUuidVersion = 2)
{
string block1, block2, block3, block4, block5;
DateTime dt;
// The sorting in SQL Server is like this:
// First Sort block 5, nibbles from left to right (i.e. 000000000001 < 000000000010 < ... < 010000000000 < 100000000000)
if (hickelUuidVersion == 1)
{
block5 = "000000000000";
}
else if (hickelUuidVersion == 2)
{
block5 = "5ce32bd83b96";
}
else
{
throw new Exception("Invalid version");
}
// Then: Sort block 4, nibbles from left to right
if (hickelUuidVersion == 1)
{
dt = DateTime.Now;
block4 = String.Format("{0:yy}{1:D2}", dt, (int)Math.Floor((decimal)dt.Year/100)); // Example: 0x2420 = 2024
}
else
{
dt = DateTime.UtcNow;
int variant = 0x8; // First nibble needs to be 0b10_ (0x8-0xB) for RFC 9562. We use it to store 2 more random bits.
int unused2bits = 0; // Cannot be used for random, because it would affect the sorting
block4 = String.Format("{0:X1}{1:X3}", variant + (unused2bits & 0x3), dt.Year);
}
// Then: Sort block 3, bytes from right to left (i.e. 0100 < 1000 < 0001 < 0010)
if (hickelUuidVersion == 1)
{
block3 = String.Format("{0:ddMM}", dt);
}
else
{
int uuidVersion = 8; // First nibble needs to be "8" for "UUIDv8 = Custom UUID"
block3 = String.Format("{0:X1}{1:X3}", uuidVersion, dt.DayOfYear/*1..366*/);
}
// Then: Sort block 2, bytes from right to left
if (hickelUuidVersion == 1)
{
block2 = String.Format("{0:mmHH}", dt);
}
else
{
int minuteOfDay = (dt.Minute + dt.Hour * 60) + 1; // 1..1440
block2 = String.Format("{0:X4}", minuteOfDay);
}
// Then: Sort block 1, bytes from right to left
int millisecond8bits = (int)Math.Round(((decimal)dt.Millisecond / 999) * 255); // deviation -2ms..2ms
if (hickelUuidVersion == 1)
{
int rnd16bits = random.Next(0x0000, 0xFFFF);
block1 = String.Format("{0:X4}{1:X2}{2:ss}", rnd16bits, millisecond8bits, dt);
}
else
{
int rnd16bits = random.Next(0x0000, 0xFFFF + 1);
block1 = String.Format("{0:X4}{1:X2}{2:X2}", rnd16bits, millisecond8bits, dt.Second);
}
// Now build and parse UUID
string sGuid = String.Format("{0}-{1}-{2}-{3}-{4}", block1, block2, block3, block4, block5);
Thread.Sleep((int)Math.Ceiling((decimal)999 / 255)); // Make sure that "millisecond" is not repeated on this system
return Guid.Parse(sGuid);
}
static void Main(string[] args)
{
Console.WriteLine(DateTime.Now);
Guid test;
test = GenerateSqlServerSortableTimeBasedGuid(1);
Console.WriteLine(test);
test = GenerateSqlServerSortableTimeBasedGuid(2);
Console.WriteLine(test);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment