I am currently tasked with converting a bunch of C# CRL code into SQL Server stored procedures and scalar functions. The task seems enough simple at first glance, until I stumbled upon a piece of code that generates a new Guid with a byte array as parameter.
The code looks like this:
public static Guid GenerateGuid()
{
var bytes = GenerateBytes();
return new Guid(bytes);
}
The bytes generated is not the concern here, what puzzles me is how C# uses this byte array to create a new Guid instance. In SQL Server, however, there is only one function NEWID() which accepts no parameter, it is similar to Guid.NewGuid().
Unfortunately it is not what I need for this use case.
So I’d like to ask there a way I can rewrite the above C# code into T-SQL to generate a new guid with specified byte array instead of just a completely random guid using NEWID()?
2
The simple answer would be to CONVERT(UNIQUEIDENTIFIER, @Bytes), where @Bytes is a BINARY(16) value, but that answer would be wrong if you need to maintain compatibility and consistency across platforms.
SQL Server stores the first 3 parts (one 4-byte integer and two 2-byte integers) in a little-endian host order that differs from the big-endian network order, so bytes 1 through 4, 5 & 6, and 7 & 8 each need to be reversed.
The following will accomplish this:
DECLARE @NetworkOrderBytes BINARY(16) = 0x00112233445566778899aabbccddeeff
DECLARE @HostOrderBytes BINARY(16) =
SUBSTRING(@NetworkOrderBytes,4,1) -- Reverse bytes 1-4 (0-3)
+ SUBSTRING(@NetworkOrderBytes,3,1)
+ SUBSTRING(@NetworkOrderBytes,2,1)
+ SUBSTRING(@NetworkOrderBytes,1,1)
+ SUBSTRING(@NetworkOrderBytes,6,1) -- Reverse bytes 5-6 (4-5)
+ SUBSTRING(@NetworkOrderBytes,5,1)
+ SUBSTRING(@NetworkOrderBytes,8,1) -- Reverse bytes 7-8 (6-7)
+ SUBSTRING(@NetworkOrderBytes,7,1)
+ SUBSTRING(@NetworkOrderBytes,9,8) -- Remaining bytes are unchanged
DECLARE @Result UNIQUEIDENTIFIER = CONVERT(UNIQUEIDENTIFIER, @HostOrderBytes)
SELECT @Result -- 00112233-4455-6677-8899-aabbccddeeff
An alternative is to convert the bytes to a hex string, inject the appropriate dashes, and then convert to UNIQUEIDENTIFIER. The last step of this approach handles the byte order issues.
DECLARE @NetworkOrderBytes BINARY(16) = 0x00112233445566778899aabbccddeeff
DECLARE @Result UNIQUEIDENTIFIER =
CONVERT(UNIQUEIDENTIFIER,
STUFF(STUFF(STUFF(STUFF(
CONVERT(VARCHAR(50), @NetworkOrderBytes, 2),
9 , 0, '-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-'))
SELECT @Result -- 00112233-4455-6677-8899-aabbccddeeff
See this db<>fiddle for a demo.
For a related discussion about byte order see SQL Server GUID sort algorithm. Why?.