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 ClassThe 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?