GUIDs (Globally Unique Identifier) can be a good choice for the ID or Key value in a database table, but it’s randomness is not good for the database when it comes to indexing the data and sorting the rows of data based on that GUID ID.

But a COMB GUID, basically a combination GUID with embedded data and time stamp is much better.  The COMB GUID (with embed date and time) becomes a sequential GUID, with each GUID being sequentially after the previous GUID.  This works great for indexing and sorting.  But you cant just replace or insert this time stamp anywhere in the GUID,  any part of the GUID with the date/time, it depends on the Database Server.  For this post, and where I work, I will refer to MS SQL Server as the database server.  It is important to replace the portion of the GUID that MSSQL sorts.    MSSQL sorts first by the last 6 Data4 bytes, left to right, then the first two bytes of Data4(again, left to right), then Data3, Data2, and Data1 right to left. This means for COMB purposes, we want to overwrite the the last 6 bytes of Data4 (byte index 10), left to right.

In the C# method below, we get a GUID.  Then we get the current date time.  From the date time, we generate a byte array for the total Days value and then a byte array for the total milliseconds value.  We then reverse the bytes to match MSSQL sorting, and then replace the last 6 bytes of the GUID with the total Days and total Milliseconds values.

 

   public class GuidCombGenerator  
   {  
     private static readonly long BaseDateTicks = new DateTime(1900, 1, 1).Ticks;  
   
     /// <summary>  
     ///   Generate a new <see cref="Guid" /> using the comb algorithm.  
     /// </summary>  
     /// <returns>New <see cref="Guid" />.</returns>  
     public Guid Generate()  
     {  
       byte[] guidArray = Guid.NewGuid().ToByteArray();  
   
       DateTime now = DateTime.UtcNow;  
   
       // Get the days and milliseconds which will be used to build the byte string   
       TimeSpan days = new TimeSpan(now.Ticks - BaseDateTicks);  
       TimeSpan msecs = now.TimeOfDay;  
   
       // Convert to a byte array   
       byte[] daysArray = BitConverter.GetBytes(days.Days);  
       byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds));  
   
       // Reverse the bytes to match SQL Servers ordering   
       Array.Reverse(daysArray);  
       Array.Reverse(msecsArray);  
   
       // Copy the bytes into the guid   
       Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2);  
       Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4);  
   
       return new Guid(guidArray);  
     }  
   }  
   

 

But what do I do with all the GUIDS already in the database that are not this new COMB GUID.  Assuming you have some date/time field in your table for date created or last date/time updated… you can use that date and generate a new COMB GUID and then update your GUID ID column.  This will update the columnName with a system GUID with the last 6 bytes replaced with the date time value pulled from the current row.

UPDATE [tableName] set [columnName] = (CAST(CAST(NEWID() AS binary(10)) + CAST(CAST([date/time columnName] AS datetime) AS binary(6)) AS uniqueidentifier))

I hope you were able to follow all of that and it becomes of some help to you.

References:

https://github.com/richardtallent/RT.Comb

https://github.com/pjvds/ncqrs/blob/master/Framework/src/Ncqrs/GuidCombGenerator.cs