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.
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:
But if you look at a SQL Server generated sequential guid it would looks like this:
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.
– UuidCreateSequential Reversed Bits
– UuidCreateSequential Reversed Bits
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.