Why generate sequential GUID in C#?
Originally, uniqueidentifier (GUID) column in SQL Server was not supposed to be sequential. But in my case, having sequential GUID is quite useful.My application needs to know, what record was inserted first.
Fortunately, SQL Server 2005 supports "default newsequentialid()" constraint, that makes uniqueidentifier column grow sequentially [with every inserted record].
That worked quite well for me, until I decided to generate sequential GUID in C#.
(I needed it, because I use SqlBulkCopy and try to save two tables that share the same generated GUID key).
That turned out to be a tricky task. The reason -- .NET and SQL Server treat GUIDs quite different. In particular, they sort them quite differently.
Searching for solution
Alberto Ferrari's post How are GUIDs sorted by SQL Server? gave me a good idea about how to handle the problem.I used modified Alberto's SQL code to find out what C#.NET GUID bytes are more [or less] significant from SQL Server 2005 ORDER BY clause perspective.
With UIDs As (
Select ID = 3, UID = cast ('01000000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 2, UID = cast ('00010000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 1, UID = cast ('00000100-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 0, UID = cast ('00000001-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 5, UID = cast ('00000000-0100-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 4, UID = cast ('00000000-0001-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 7, UID = cast ('00000000-0000-0100-0000-000000000000' as uniqueidentifier)
Union Select ID = 6, UID = cast ('00000000-0000-0001-0000-000000000000' as uniqueidentifier)
Union Select ID = 8, UID = cast ('00000000-0000-0000-0100-000000000000' as uniqueidentifier)
Union Select ID = 9, UID = cast ('00000000-0000-0000-0001-000000000000' as uniqueidentifier)
Union Select ID = 10, UID = cast ('00000000-0000-0000-0000-010000000000' as uniqueidentifier)
Union Select ID = 11, UID = cast ('00000000-0000-0000-0000-000100000000' as uniqueidentifier)
Union Select ID = 12, UID = cast ('00000000-0000-0000-0000-000001000000' as uniqueidentifier)
Union Select ID = 13, UID = cast ('00000000-0000-0000-0000-000000010000' as uniqueidentifier)
Union Select ID = 14, UID = cast ('00000000-0000-0000-0000-000000000100' as uniqueidentifier)
Union Select ID = 15, UID = cast ('00000000-0000-0000-0000-000000000001' as uniqueidentifier)
)
Select * From UIDs Order By UID
Note, that first line with ID=3 corresponds to:
new Guid(new bytes[16]{ 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }
That means:
(new Guid("01000000-0000-0000-0000-000000000000").ToByteArray()[3] == 1)
Now, when I run modified Alberto's query, I'm getting the following sequence:
3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10
That means, that GUID's byte #3 is the least significant and GUID's byte #10 is the most significant [from SQL Server ORDER BY clause perspective].
Final solution
Now we're ready to write C# code, that would sequentially increment any given GUID.I also made it more convenient to increment GUID, by using "++" operator.
Here's how it's used:
private void Test()
{
SequentialGuid = new SequentialGuid(Guid.Empty);
SequentialGuid++;
}
and C# code that increments GUIDs sequentially:
public class SequentialGuid
{
Guid _CurrentGuid;
public Guid CurrentGuid
{
get { return _CurrentGuid; }
}
public SequentialGuid()
{
_CurrentGuid = Guid.NewGuid();
}
public SequentialGuid(Guid previousGuid)
{
_CurrentGuid = previousGuid;
}
public static SequentialGuid operator ++(SequentialGuid sequentialGuid)
{
byte[] bytes = sequentialGuid._CurrentGuid.ToByteArray();
for (int mapIndex = 0; mapIndex < 16; mapIndex++)
{
int bytesIndex = SqlOrderMap[mapIndex];
bytes[bytesIndex]++;
if (bytes[bytesIndex] != 0)
{
break; // No need to increment more significant bytes
}
}
sequentialGuid._CurrentGuid = new Guid(bytes);
return sequentialGuid;
}
private static int[] _SqlOrderMap = null;
private static int[] SqlOrderMap
{
get
{
if (_SqlOrderMap == null)
{
_SqlOrderMap = new int[16] { 3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10 };
// 3 - the least significant byte in Guid ByteArray [for SQL Server ORDER BY clause]
// 10 - the most significant byte in Guid ByteArray [for SQL Server ORDERY BY clause]
}
return _SqlOrderMap;
}
}
}
