How to Generate Sequential GUIDs for SQL Server in .NET


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

UuidCreateSequential http://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 http://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 (7)

  1. Coder says:

    what an idiot ? He/She doesnot even know how to use loops with array

  2. ALBDVM says:

    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

  3. ZHaDoom says:

    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.

  4. gregthatcher says:

    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%

  5. Mohsin says:

    Thanks a Lot !!!!!

    Your help really saved me from lot of hassle of changing old code to add that sequential functionality…

    Thanks and God bless you

  6. Leo says:

    Thanks for that code snippet. It really increases the performance like crazy.

  7. Grig says:

    Excellent article, thank you.