SQL Server UniqueIdentifier / GUID internal representation

A colleague of mine sent me an interesting question, that I can't entirely explain.

He ran some code (included below) and got somewhat unexpected results from it.

Essentially, when converting a UniqueIdentifier (which I'll refer to as Guid from here on out) to a binary (or varbinary) type, the order of the first half of the result is backwards, but the second half is not.

My first thought was that the endianness of the system was the cause, and that the Guid display was preserved, but the binary form is not guaranteed.

Obviously this is an implementation detail, but I was wondering if there were a good explanation for it.

Code:

declare @guid uniqueidentifier = '8A737954-CBEC-40CE-A534-2AFFB5A0E207';
declare @binary binary(16) = (select convert(binary(16), @guid));
select @guid as [GUID], @binary as [Binary];

Results:

GUID                                 Binary
8A737954-CBEC-40CE-A534-2AFFB5A0E207 0x5479738AECCBCE40A5342AFFB5A0E207

As you can see, the first half of the Guid (all the way through 40CE) is stored backwards for each section. That is, the first section of the Guid is backwards, then the second section, then the third section, but the order of the sections is preserved. After that, the last two sections are stored in the exact order they appear in the Guid.

Can anyone explain this? (A larger test set is included below.)

Code:

declare @guid_to_binary table
(
    [id] int identity(1,1),
    [guid] uniqueidentifier,
    [binary_conversion] binary(16)
);
declare @i int = 1;
while @i <= 100
begin
    insert into @guid_to_binary ( [guid] )
    select newid();
    set @i = @i + 1;
end
update @guid_to_binary
set binary_conversion = convert(binary(16), [guid]);
select * from @guid_to_binary;

Results:

```
id guid binary_conversion
1 EBA4DE58-D35F-46D9-9049-7939381F1A3B 0x58DEA4EB5FD3D94690497939381F1A3B
2 0445A8B7-B2F1-4B35-A4DC-92133570CD69 0xB7A84504F1B2354BA4DC

According to the Wikipedia article on Globally unique identifier (in the "Binary encoding" section), Microsoft's implementation of GUIDs uses "Native" endianness for the first half (the first 8 bytes), and Big Endian encoding for the second 8 bytes. Microsoft Windows and SQL Server are "Little Endian" due to the Intel architecture being Little Endian, hence the "Native" implies "Little Endian". The following chart is copied from that Wikipedia article:

Bits    Bytes   Name    Endianness
32      4       Data1   Native
16      2       Data2   Native
16      2       Data3   Native
64      8       Data4   Big

Regarding Little Endianness of Microsoft SQL Server, the Collation and Unicode Support MSDN page states:


Because the Intel platform is a little endian architecture, Unicode code characters are always stored byte-swapped.

StackExchange Database Administrators Q#121869, answer score: 19