Unraveling the mysteries of NewSequentialID

A few months ago we started development on a new system. From the ground up we redesigned everything from our business processes to how we store data. Part of the redesign was deciding on using SOA technology in our middle tier. This will allow us to separate our UI from our middle tier even further. Due to the disconnected way our objects were going to work we decided to use guid or unique identifiers for our primary keys in our data model and assign guids in the middle tier. Of course, our DBAs weren’t too keen on the idea and even now they are still a bit gunshy but they’re coming around.

Of course, is one of the big concerns that the DB team has is the performance of SQL Server with GUIDs which has been well documented all across the net. So we decided to use the NewSequentialID() function as the default for all guid primary keys. On the application side we decided to implement a GUIDComb which was created by Jimmy Nilsson. The issue that we ran into with the GUIDComb was that it was creating guid sequence that was different from the NewSequentialID guid sequence.

At this point I decided to jump into the Google abyss and see if anyone else conquered this problem. The first solution that I found took a guid generated from System.Guid.NewGuid and reordered it so SQL Server would sort it correctly. The major issue that I found with this approach was the fact that it incremented the guid internally and didn’t store the guid in the SQL Server sequence.

It was at this time that I found and excellent article by Rob Garrison entitled “Exploring NewSequentialID() in SQL Server 2005“. In the article he discusses the differences between NewID() and NewSequenceID() and how NewSequenceID() uses a shared counter. So if you insert a row into Table1 then Table2 then Table1 again you will see the following GUID sequence.

63DD0AD3-A111-DD11-B2EB-005056AE063C

– Table1

64DD0AD3-A111-DD11-B2EB-005056AE063C

– Table2

65DD0AD3-A111-DD11-B2EB-005056AE063C

– Table1

That article led me to another article “How are GUIDs sorted by SQL Server?” Which linked to to the SQL Programmability & API Development Team Blog where I found this gem “Newsequentialid (Histrory/Benefits and Implementation)“. In this post, they explain that sequential guids are created by calling UuidCreateSequential function of the rpcrt4.dll “with some byte scrambling to convince the rest of SQL engine that guids are produced in sequential order”. Here’s an example of the usage. The output of two calls to the UuidCreateSequential function would look like this:

6F9F9BFB-1203-11DD-BC40-001641E22FDB
6F9F9BFC-1203-11DD-BC40-001641E22FDB

But if you look at a SQL Server generated sequential guid it would looks like this:

4782336F-0312-DD11-B2EB-005056AE063C
4882336F-0312-DD11-B2EB-005056AE063C

And that’s when it hit me, that “byte scrambling” that SQL Server was doing was in the first four bytes. If your guid is 6F9F9BFB-1203-11DD-BC40-001641E22FDB the first for bytes would be 6F 9F 9B FB. If you reverse them, FB 9B 9F 6F that would be in the sequence that SQL Server is currently using.

6F9F9BFB-1203-11DD-BC40-001641E22FDB

– UuidCreateSequential

6F9F9BFC-1203-11DD-BC40-001641E22FDB

– UuidCreateSequential

FB9B9F6F-1203-11DD-BC40-001641E22FDB

– UuidCreateSequential Reversed Bits

 
FC9B9F6F-1203-11DD-BC40-001641E22FDB

– UuidCreateSequential Reversed Bits

4782336F-0312-DD11-B2EB-005056AE063C

– NewSequentialID

4882336F-0312-DD11-B2EB-005056AE063C

– NewSequentialID

Here’s the code I created to create SQL Server formatted sequential guids in .Net:

Public Class SequentialGuid
    Declare Function UuidCreateSequential Lib "rpcrt4.dll" (ByRef id As Guid) As Integer

    Public Shared Function CreateSequentialGUID() As Guid

        Dim newGuid As Guid
        Dim returnVal As Integer
        returnVal = UuidCreateSequential(newGuid)

        Dim bytes As Byte() = newGuid.ToByteArray

        If returnVal <> 0 Then
            Throw New Exception("CreateSequentialGUID failed.")
        Else
            Array.Reverse(bytes, 0, 4)
            Array.Reverse(bytes, 4, 2)
            Array.Reverse(bytes, 6, 2)
            newGuid = New Guid(bytes)

            Return newGuid
        End If
    End Function
End Class

The one caveat is that DB generated guids and .Net generated guids may be not reflect the order in which the rows were inserted due to the mac address in the guid and the way SQL sorts its guids. (See the “How are GUIDs sorted by SQL Server?” article for more details.) You may see blocks of guids in your table generated by different machines. So if you need to know the true sequence of when a row was added to a table then use a created date field and the GetDate function.

Other Sources:

Improve Your Guid Key Performance with GuidCombs

The Cost of GUIDs as Primary Keys

The NewSequentialID Function

Generate Sequential GUIDs for SQL Server 2005 in C#

Sequential GUID Generator on Codeplex

UuidCreateSequential rptcrt4.dll

SqlGuid Structure

How are GUIDs compared in SQL Server 2005?

3 thoughts on “Unraveling the mysteries of NewSequentialID

  1. Hi,

    I have run into a situation similar to yours. Disconnected object layer and (eventually) a distributed (peer-to-peer replicated) SQL Server DB environment. I had settled on UniqueIdentifier for all primary keys in the DB to reduce potential for future problems. I had also mandated NewSequentialId() as the default value because it strikes a nice compromise between the performance of Identity and the flexibility of UniqueIdentifier.

    However, we had also been seriously considering the .NET Entity Framework in our middle tier. It turns out that the current version of the framework does not support primary keys with default value of NewId() or NewSequentialId(). This is very aggravating.

    Anyhow, I’ve been casting about for a solid compromise and had landed on the GUIDcomb solution but was suspicious of its behaviour in SQL Server with regards to sorting and indexing. My search eventually led me here, and it looks like you’ve saved me a ton of research. Now I can start to experiment with this. Thank you very much.

    Is your solution working out as you expected?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>