I wrote a blog post called How SQL Server Sorts the UNIQUEIDENTIFIER Type and another one called Ordering the SQL UNIQUEIDENTIFIER Type Numerically Correct for Reporting a while back. As a result, I get a lot of e-mails from people struggling with UNIQUEIDENTIFIER values in Microsoft SQL Server. That's cool because I like helping other developers. The mistake that most people make when working with this data type is treating them like strings. However, UNIQUEIDENTIFIERS are absurd looking integers, really big ones. We show them in hexadecimal format to make them more compact which adds to their absurdness, I suppose.

As I demonstrated in my previous blog posts, SQL Server adds to the absurdity by making the readable version of UNIQUEIDENTIFIER values fundamentally different from their numerical handling. For example, a UNIQUEIDENTIFIER that reads as FFEEDDCC-BBAA-9988-6677-001122334455 in a SQL script will be treated as an integer that we humans would read from left to right as 00112233-4455-6677-8899-AABBCCDDEEFF. We expect the most significant digits of a number to appear on the left and the least significant digits to appear on the right. But SQL Server doesn't work that way. Here's some T-SQL code that will create a table called [TestValue] and populate it with some UNIQUEIDENTIFIER values.

SET ansi_nulls ON
GO
 
SET quoted_identifier ON
GO
 
CREATE TABLE [dbo].[testvalue]
 (
 [rowid] [INT] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
 [uid] [UNIQUEIDENTIFIER] NOT NULL,
 [readableuid] [NCHAR](36) NULL
 )
GO
 
CREATE FUNCTION [dbo].[Numericallycorrectuid] (@uid UNIQUEIDENTIFIER)
RETURNS NCHAR(36)
AS
 BEGIN
 DECLARE @RESULT NCHAR(36)
 
 SET @RESULT = CONVERT(NCHAR(36), @uid)
 SET @RESULT = SUBSTRING(@RESULT, 25, 8) + N'-'
 + RIGHT(@RESULT, 4) + SUBSTRING(@RESULT, 19, 6)
 + SUBSTRING(@RESULT, 17, 2)
 + SUBSTRING(@RESULT, 15, 2) + N'-'
 + SUBSTRING(@RESULT, 12, 2)
 + SUBSTRING(@RESULT, 10, 2)
 + SUBSTRING(@RESULT, 7, 2)
 + SUBSTRING(@RESULT, 5, 2)
 + SUBSTRING(@RESULT, 3, 2) + LEFT(@RESULT, 2)
 
 RETURN @RESULT
 END
GO
 
CREATE TRIGGER [dbo].[trg_UpdateReadableUid]
ON [dbo].[testvalue]
AFTER INSERT
AS
 BEGIN
 UPDATE [TV]
 SET [readableuid] = dbo.Numericallycorrectuid([TV].[uid])
 FROM [dbo].[testvalue] AS [TV]
 JOIN inserted AS [I]
 ON [TV].[uid] = [I].[uid]
 END
GO
 
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
INSERT INTO [UUIDTest].[dbo].[testvalue] ([uid]) VALUES (Newid())
GO
 
SELECT [rowid],
 [uid],
 [readableuid]
FROM [dbo].[testvalue]
ORDER BY [rowid] 

The query at the end shows the UNIQUEIDENTIFIER values in the order that they were inserted. On my computer, they appear as follows. Please understand that on your computer you will get different values. If you didn't, we would have to remove the UNIQUE from the data type's name, wouldn't we?

RowId UID ReadableUID
1 21321236-C387-4F81-83C5-201B3ECCFFC9 201B3ECC-FFC9-83C5-814F-87C336123221
2 4159FB16-F10C-4C03-AABD-6A6BBB092ABA 6A6BBB09-2ABA-AABD-034C-0CF116FB5941
3 0F4F2022-BAB4-411C-B66B-8C63167987B7 8C631679-87B7-B66B-1C41-B4BA22204F0F
4 5F326809-C47A-4149-AAA3-8E3F1C8419A2 8E3F1C84-19A2-AAA3-4941-7AC40968325F
5 31243180-AFB1-427A-A8D9-04EEA9866224 04EEA986-6224-A8D9-7A42-B1AF80312431
6 B1731F5E-13BA-4683-A846-020D7121FDEB 020D7121-FDEB-A846-8346-BA135E1F73B1
7 FABD2006-D8CF-44EE-8774-7D3052FF5A28 7D3052FF-5A28-8774-EE44-CFD80620BDFA
8 A4547257-E3C5-4EEF-ABC0-246D96DAE4A1 246D96DA-E4A1-ABC0-EF4E-C5E3577254A4
9 B03AF9E2-583F-44A6-B99D-169457FFA629 169457FF-A629-B99D-A644-3F58E2F93AB0
10 502FA784-5308-4A33-9F1A-36816860BB61 36816860-BB61-9F1A-334A-085384A72F50

The first thing to notice is that the UNIQUEIDENTIFIERs were inserted in what seems like random order. This is because I used the NEWID() function in my INSERT statements to generate the UNIQUEIDENTIFIER values. If I had used the NEWSEQUENTIALID() function instead, the values would have been in ascending order when sorted by the [RowId]. The second thing to take note of is that comparing the [ReadableID] version of each [UID] reveals the pattern I showed above. Namely, the bytes of the [ReadableID] represented as 00112233-4455-6677-8899-AABBCCDDEEFF show in each related [UID] in the order FFEEDDCC-BBAA-9988-6677-001122334455. Now, let's order the results differently:

SELECT [rowid],
 [uid],
 [readableuid]
FROM [dbo].[testvalue]
ORDER BY [uid]
RowId UID ReadableUID
6 B1731F5E-13BA-4683-A846-020D7121FDEB 020D7121-FDEB-A846-8346-BA135E1F73B1
5 31243180-AFB1-427A-A8D9-04EEA9866224 04EEA986-6224-A8D9-7A42-B1AF80312431
9 B03AF9E2-583F-44A6-B99D-169457FFA629 169457FF-A629-B99D-A644-3F58E2F93AB0
1 21321236-C387-4F81-83C5-201B3ECCFFC9 201B3ECC-FFC9-83C5-814F-87C336123221
8 A4547257-E3C5-4EEF-ABC0-246D96DAE4A1 246D96DA-E4A1-ABC0-EF4E-C5E3577254A4
10 502FA784-5308-4A33-9F1A-36816860BB61 36816860-BB61-9F1A-334A-085384A72F50
2 4159FB16-F10C-4C03-AABD-6A6BBB092ABA 6A6BBB09-2ABA-AABD-034C-0CF116FB5941
7 FABD2006-D8CF-44EE-8774-7D3052FF5A28 7D3052FF-5A28-8774-EE44-CFD80620BDFA
3 0F4F2022-BAB4-411C-B66B-8C63167987B7 8C631679-87B7-B66B-1C41-B4BA22204F0F
4 5F326809-C47A-4149-AAA3-8E3F1C8419A2 8E3F1C84-19A2-AAA3-4941-7AC40968325F

Zero in on the [RowId] values first. They are out of order because we ordered by the [UID] instead. But glancing at the [UID] and thinking of them as integers, they don't look ordered either. Look at the first digit of the first two rows, digit B (value 11) certainly comes after 3 numerically. So, how could this be ordered properly? Now look at the [ReadableID] column. Lo, and behold, that column appears to be sorted in ascending fashion. But we didn't order by that column so what's going on here? Again, the way we see a UNIQUEIDENTIFIER as human beings and the way SQL treats these really large integers is quite different. And these differences persist even when we humans try to convey UNIQUEIDENTIFIERs as strings in our scripts. For example, if I wanted to use the ordering by [UID] shown in the last query and return the last five rows ([RowId] 10, 2, 7, 3 and 4) by using the greater than or equal operator, I could do it as follows:

SELECT [rowid],
 [uid],
 [readableuid]
FROM [dbo].[testvalue]
WHERE [uid] >= '502FA784-5308-4A33-9F1A-36816860BB61'
ORDER BY [uid]

Which would return the last 5 rows of the query shown before as:

RowId UID ReadableUID
10 502FA784-5308-4A33-9F1A-36816860BB61 36816860-BB61-9F1A-334A-085384A72F50
2 4159FB16-F10C-4C03-AABD-6A6BBB092ABA 6A6BBB09-2ABA-AABD-034C-0CF116FB5941
7 FABD2006-D8CF-44EE-8774-7D3052FF5A28 7D3052FF-5A28-8774-EE44-CFD80620BDFA
3 0F4F2022-BAB4-411C-B66B-8C63167987B7 8C631679-87B7-B66B-1C41-B4BA22204F0F
4 5F326809-C47A-4149-AAA3-8E3F1C8419A2 8E3F1C84-19A2-AAA3-4941-7AC40968325F

When using the UNIQUEIDENTIFIER for the first row I want, it's important to note that I have to use SQL Server's numerically significant format of 502FA784-5308-4A33-9F1A-36816860BB61 instead of the readable-as-integer 36816860-BB61-9F1A-334A-085384A72F50. In fact, if I were to try to use the version that allows me to read the value from left to right as an integer instead, I would get very different results. Try it for yourself to see what happens.