OHMS BLOG

Monday, April 25, 2011

code

How to generate your own sequential GUIDs for use with Microsoft SQL Server

Let's suppose that you want to create a table whose primary key is a uniqueidentifier and uses a clustered index. You may certainly use the NEWSEQUENTIALID() function to supply the next sequential GUID as a default. On the other hand, what do you do if you want to generate such a GUID in your application?


The answers are hidden in plain sight: The documentation for NEWSEQUENTIALID() states that this function wraps UuidCreateSequential(). At the same time, this blog post indicates that SQL Server slightly modifies the GUID before inserting the value as a default.

At this point, it's easy to see for yourself what SQL Server is doing to the GUID:

  1. Attach WinDbg to sqlservr.exe, enter bp rpcrt4!UuidCreateSequential, and resume the program.
  2. Insert a row into a table such that NEWSEQUENTIALID() will be invoked.
  3. When SQL Server triggers the breakpoint, enter the kb 1 command. Take note of the first argument to UuidCreateSequential().
  4. Enter the gu command.
  5. Type db address L16, where address is the argument from step 3. The output from this command is the raw bytes of the GUID structure that was generated.
  6. Resume the debugger.
  7. Query for the newly inserted row and inspect the GUID that SQL Server inserted into the database.
You'll find that the resulting GUID is identical to the GUID from step 5, except that the Data1, Data2, and Data3 fields have been converted to big-endian representation (RFC4122 actually recommends that GUIDs be big-endian encoded, but the Microsoft implementation uses little-endian fields).

Release 7.0; Copyright © 1996-2012 Aaron Klotz. All Rights Reserved.