Lesson Learned #497:Understanding the Ordering of uniqueidentifier in SQL Server

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer asked about how SQL Server sorts the uniqueidentifier data type. We know that uniqueidentifier store globally unique identifiers (GUIDs). GUIDs are widely used for unique keys due to their extremely low probability of duplication. One common method to generate a GUID in SQL Server is by using the NEWID() function. However, the ordering of GUIDs, especially those generated by NEWID(), can appear non-intuitive. I would like to share my lessons learned how to determine the ordering method using uniqueidentifier and NEWID().

 

 

 

We know that a GUID is a 16-byte value typically represented in a standard format of 32 hexadecimal characters separated by hyphens, for example, EB37F277-8A16-4DE3-B1E3-FFFCBA956A82.

 

 

 

Each section represents different parts of the GUID:

 

• The first 8 characters (4 bytes)
  
• The next 4 characters (2 bytes)
  
• The following 4 characters (2 bytes)
  
• The next 4 characters (2 bytes)
  
• The final 12 characters (6 bytes)
  

 

 

 

When we generated a NEWID() function generates a random GUID. This randomness ensures uniqueness but can lead to seemingly arbitrary ordering when GUIDs are compared and sorted. The comparison is done byte-by-byte from left to right in lexicographical order.

 

 

 

Consider the following GUIDs generated by NEWID():

 

 

 

1. C2EB258A-147F-4F26-97D7-0000F053CCA8
   
2. 6682BC4F-949E-478F-BBA2-0003C71CD035
   
3. A6D423B8-07F3-4F13-B34E-0004DC3317B7
   
4. EE2C394E-7AB0-468F-B959-0005C0CC598D
   

[HEADING=2]How the ordering works: [/HEADING]

 

To understand how SQL Server orders these GUIDs, we need to break them down into their binary components and compare each part:

 

 

 

1. 
   C2EB258A-147F-4F26-97D7-0000F053CCA8
    
   
   • Binary: C2 EB 25 8A 14 7F 4F 26 97 D7 00 00 F0 53 CC A8
     

[*]

6682BC4F-949E-478F-BBA2-0003C71CD035

 

• Binary: 66 82 BC 4F 94 9E 47 8F BB A2 00 03 C7 1C D0 35
  

[*]

A6D423B8-07F3-4F13-B34E-0004DC3317B7

 

• Binary: A6 D4 23 B8 07 F3 4F 13 B3 4E 00 04 DC 33 17 B7
  

[*]

EE2C394E-7AB0-468F-B959-0005C0CC598D

 

• Binary: EE 2C 39 4E 7A B0 46 8F B9 59 00 05 C0 CC 59 8D
  

 

When these GUIDs are ordered, SQL Server compares each byte in sequence. The final sorted order is:

 

1. 6682BC4F-949E-478F-BBA2-0003C71CD035
   
2. A6D423B8-07F3-4F13-B34E-0004DC3317B7
   
3. C2EB258A-147F-4F26-97D7-0000F053CCA8
   
4. EE2C394E-7AB0-468F-B959-0005C0CC598D
   

[HEADING=3] [/HEADING]

 

To better understand and work with GUIDs, you can create SQL functions that decompose GUIDs into their components and show their ordering. To understand how the GUID is decomposed I would like to share this function.

 

 

 

 

 

CREATE FUNCTION dbo.DescomponerGUIDString (@guid UNIQUEIDENTIFIER)
RETURNS NVARCHAR(100)
AS
BEGIN
   DECLARE @part1 NVARCHAR(8) = CONVERT(NVARCHAR(8), SUBSTRING(CONVERT(BINARY(16), @guid), 1, 4), 2);
   DECLARE @part2 NVARCHAR(4) = CONVERT(NVARCHAR(4), SUBSTRING(CONVERT(BINARY(16), @guid), 5, 2), 2);
   DECLARE @part3 NVARCHAR(4) = CONVERT(NVARCHAR(4), SUBSTRING(CONVERT(BINARY(16), @guid), 7, 2), 2);
   DECLARE @part4 NVARCHAR(4) = CONVERT(NVARCHAR(4), SUBSTRING(CONVERT(BINARY(16), @guid), 9, 2), 2);
   DECLARE @part5 NVARCHAR(12) = CONVERT(NVARCHAR(12), SUBSTRING(CONVERT(BINARY(16), @guid), 11, 6), 2);
   
   RETURN @part1 + '-' + @part2 + '-' + @part3 + '-' + @part4 + '-' + @part5;
END;

 

 

 

 

 

For example, we have this table and we are going to sort using the [Guid No_] column with NewID().

 

 

 

select [GUID No_], dbo.DescomponerGUIDString([GUID No_]) as x from dbo.ExampleTable
ORDER BY 1

 

 

 

 

 

 

 

 

For example, we have this table and we are going to sort using the sorted calculation column with NewID().

 

 

 

select [GUID No_], dbo.DescomponerGUIDString([GUID No_]) as x from dbo.ExampleTable
ORDER BY 2

 

 

 

 

 

 

Continue reading...