Sequence generator

Question

Is there a way to get the functionality of the Sql Server 2005+ Sequential Guid generator without inserting records, to read it back both ways or to call win dll win? I saw someone respond using rpcrt4.dll, but I'm not sure if this will work from my hosted production environment.

Edit: Work with @John Boker answer. I tried turning it into a GuidComb generator instead of relying on the last Guid generated, other than the launch. This is for 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 on 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 mean that I want the seeds of the guide using DateTime, or does it look like I should do this in the reverse order and work backward from the end of the SqlOrderMap indexes? I'm not too worried that they will interrupt the search call at any time when the initial guid is created, since it will only occur during reuse of the application.

Answer 1

this person came up with something to make consistent contours, here is the link

http://developmenttips.blogspot.com/2008/03/generate-sequential-guids-for-sql.html

corresponding code:

 public class SequentialGuid { Guid _CurrentGuid; public Guid CurrentGuid { get { return _CurrentGuid; } } public SequentialGuid() { _CurrentGuid = Guid.NewGuid(); } public SequentialGuid(Guid previousGuid) { _CurrentGuid = previousGuid; } public static SequentialGuid operator++(SequentialGuid sequentialGuid) { byte[] bytes = sequentialGuid._CurrentGuid.ToByteArray(); for (int mapIndex = 0; mapIndex < 16; mapIndex++) { int bytesIndex = SqlOrderMap[mapIndex]; bytes[bytesIndex]++; if (bytes[bytesIndex] != 0) { break; // No need to increment more significant bytes } } sequentialGuid._CurrentGuid = new Guid(bytes); return sequentialGuid; } private static int[] _SqlOrderMap = null; private static int[] SqlOrderMap { get { if (_SqlOrderMap == null) { _SqlOrderMap = new int[16] { 3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10 }; // 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] } return _SqlOrderMap; } } } 

Answer 2

You can simply use the same Win32 API function that SQL Server uses :

 UuidCreateSequential 

and apply some bit offset to put the values ​​in the ordinary 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 peer .NET for SQL servers newsequentialid ()

---

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

Weed has three important parts:

• node : (6 bytes) - MAC address of the computer
• timestamp : (7 bytes) - the number of intervals of 100 ns from 00: 00: 00.00, October 15, 1582 (date of repression in the Gregorian style in the Christian calendar).
• clockSequenceNumber (2 bytes) - counter in case you create a guid faster than 100ns, or you change your MAC address

The main algorithm:

• get system lock
• read the latest node , timestamp and clockSequenceNumber from persistent storage (registry / file)
• get current node (i.e. MAC address)
• get current timestamp
• • a) if the saved state is unavailable or damaged, or the mac address has changed, generate a random clockSequenceNumber
  • b) if the state is available, but the current timestamp is the same or older than the saved timestamp, increase clockSequenceNumber
• save node , timestamp and clockSequenceNumber back to persistent storage
• release global lock
• format the guide structure according to rfc

There is a 4-bit version number and a 2-bit version, which should also be indicated in 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 : fully unverified; I just looked at him from the RFC.

• you may need to change the byte order ( Here is the byte order for sql server )
• you can create your own version, for example. Version 6 (version 1-5 defined). Thus, you are guaranteed to be universally unique.

Answer 3

Here is how NHibernate implements the Guid.Comb algorithm:

 private Guid GenerateComb() { byte[] guidArray = Guid.NewGuid().ToByteArray(); DateTime baseDate = new DateTime(1900, 1, 1); DateTime now = DateTime.Now; // Get the days and milliseconds which will be used to build the byte string TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks); TimeSpan msecs = now.TimeOfDay; // Convert to a byte array // Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333 byte[] daysArray = BitConverter.GetBytes(days.Days); byte[] msecsArray = BitConverter.GetBytes((long) (msecs.TotalMilliseconds / 3.333333)); // 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); } 

Answer 4

A sequential pointer that is frequently updated (at least 3 times per millisecond) can be found here . This is a creation with regular C # code (without calling the internal code).

Answer 5

My solution (in VB, but easy to convert). It changes the most significant (for SQL Server sorting) first 8 bytes of the GUID to DateTime.UtcNow.Ticks, and also has additional code that helps solve the problem of getting the same Ticks several times if you call the new GUID faster than the clock update system.

 Private ReadOnly _toSeqGuidLock As New Object() ''' <summary> ''' Replaces the most significant eight bytes of the GUID (according to SQL Server ordering) with the current UTC-timestamp. ''' </summary> ''' <remarks>Thread-Safe</remarks> <System.Runtime.CompilerServices.Extension()> _ Public Function ToSeqGuid(ByVal guid As Guid) As Guid Static lastTicks As Int64 = -1 Dim ticks = DateTime.UtcNow.Ticks SyncLock _toSeqGuidLock If ticks <= lastTicks Then ticks = lastTicks + 1 End If lastTicks = ticks End SyncLock Dim ticksBytes = BitConverter.GetBytes(ticks) Array.Reverse(ticksBytes) Dim guidBytes = guid.ToByteArray() Array.Copy(ticksBytes, 0, guidBytes, 10, 6) Array.Copy(ticksBytes, 6, guidBytes, 8, 2) Return New Guid(guidBytes) End Function 

Answer 6

C # Version

  public static Guid ToSeqGuid() { Int64 lastTicks = -1; long ticks = System.DateTime.UtcNow.Ticks; if (ticks <= lastTicks) { ticks = lastTicks + 1; } lastTicks = ticks; byte[] ticksBytes = BitConverter.GetBytes(ticks); Array.Reverse(ticksBytes); Guid myGuid = new Guid(); byte[] guidBytes = myGuid.ToByteArray(); Array.Copy(ticksBytes, 0, guidBytes, 10, 6); Array.Copy(ticksBytes, 6, guidBytes, 8, 2); Guid newGuid = new Guid(guidBytes); string filepath = @"C:\temp\TheNewGuids.txt"; using (StreamWriter writer = new StreamWriter(filepath, true)) { writer.WriteLine("GUID Created = " + newGuid.ToString()); } return newGuid; } } 

}

Answer 7

As far as I know, NHibernate has a special generator called GuidCombGenerator. You can look at it.

Answer 8

It might be interesting to compare with other offers:

The EntityFramework core also implements a serial GuidValueGenerator. They generate randoms hints for each value and only change the most important bytes based on timestamp and thread safe increments for sorting in SQL Server.

source link

This leads to values ​​that are all very different, but sorted by time.

Answer 9

I just saw this question ... I happen to be the author of a small open source .NET library for generating COMB-style GUIDs.

The library supports both the original method (compatible with the SQL Server datetime type) and one using Unix timestamps that have more time. It also includes an option that works best for PostgrSQL:

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

Answer 10

Not specifically, but now I usually use the Snowflake style sequential identifier generator. The same leadership benefits, although they have better cluster index compatibility than a sequential index.

Flakey for .NET Core

IdGen for .NET Framework

Answer 11

I just accepted the answer to NHibernate Muslim Ben Dhau and made it an extension function:

 using System; namespace Atlas.Core.Kernel.Extensions { public static class Guids { public static Guid Comb(this Guid source) { byte[] guidArray = source.ToByteArray(); DateTime baseDate = new DateTime(1900, 1, 1); DateTime now = DateTime.Now; // Get the days and milliseconds which will be used to build the byte string TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks); TimeSpan msecs = now.TimeOfDay; // Convert to a byte array // Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333 byte[] daysArray = BitConverter.GetBytes(days.Days); byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds / 3.333333)); // 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); } } }