How to Generate Sequential GUIDs for SQL Server in .NET

  • Статья

There’s a Windows function to create sequential GUIDs, called

UuidCreateSequential https://msdn.microsoft.com/en-us/library/windows/desktop/aa379322(v=vs.85).aspx

But SQL Server uses a different sort order for GUIDs, and the results of UuidCreateSequential  are not sequential with respect to SQL Server’s sort order.  To make them sequential SQL Server’s internal NEWSEQUENTIALID function performs some byte shuffling on the GUID.

NEWSEQUENTIALID https://msdn.microsoft.com/en-us/library/ms189786.aspx

If you want to generate sequential GUIDs in application code you need to perform the same byte shuffling.  I compared the output of UuidCreateSequentiald and NEWSEQUENTIALID  to come up with the mapping and the following C# function that uses UuidCreateSequential to generate GUIDs that are sequential according to SQL Server’s GUID sort order:

  public class SQLGuidUtil

  {

      [DllImport("rpcrt4.dll", SetLastError = true)]

      static extern int UuidCreateSequential(out Guid guid);

 

      public static Guid NewSequentialId()

      {

        Guid guid;

        UuidCreateSequential(out guid);

        var s = guid.ToByteArray();

        var t = new byte[16];

        t[3] = s[0];

        t[2] = s[1];

        t[1] = s[2];

        t[0] = s[3];

        t[5] = s[4];

        t[4] = s[5];

        t[7] = s[6];

        t[6] = s[7];

        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);

      }

  }

Comments

  • Anonymous
    September 20, 2013
    what an idiot ? He/She doesnot even know how to use loops with array

  • Anonymous
    October 20, 2013
    Well, Genius, instead of being insulting and rude, why don't you try be useful for once and offer him a suggestion or rework his code. At least he is trying to help other programmers and share. More than what can be said about you. And, by the way, What should be capitalized as it is at the beginning of a sentence, there should be a space between does and not, and you should pluralize array. What an ignorant condescending moron. Regards, Alberto

  • Anonymous
    July 25, 2014
    Thanks,  just what I was looking for. To beat a dead horse.....  Coder  the order of the byte-array copy is not liner .  The logic to address this in a loop would result in inefficient code.

  • Anonymous
    August 04, 2014
    Thanks so much!  I couldn't figure out why UuidCreateSequential guids weren't sequential in my table.  You just increased my SQL performance (when using an index on a guid) by like 1000%

  • Anonymous
    December 28, 2014
    The comment has been removed

  • Anonymous
    August 17, 2015
    The comment has been removed

  • Anonymous
    March 16, 2016
    Excellent article, thank you.