SA Developer .NET

I've always figures SQL Server was intelligent, but today it called me a liar. :P

I don't often sort on UNIQUEIDENTIFIER columns but for a master table data load script I decided to keep the data consistent so I could see changes in source control easily. But alas the results were not quite as I had expected. Take the following snippet as an example:

CREATE TABLE [dbo].[Foo] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED
) ON [PRIMARY];
GO

INSERT INTO [dbo].[Foo] VALUES ('00000000-0000-FFFF-0000-000000000000');
INSERT INTO [dbo].[Foo] VALUES ('FFFFFFFF-0000-0000-0000-000000000000');
INSERT INTO [dbo].[Foo] VALUES ('00000000-0000-0000-0000-FFFFFFFFFFFF');
INSERT INTO [dbo].[Foo] VALUES ('00000000-FFFF-0000-0000-000000000000');
INSERT INTO [dbo].[Foo] VALUES ('00000000-0000-0000-0000-000000000000');
INSERT INTO [dbo].[Foo] VALUES ('00000000-0000-0000-FFFF-000000000000');
GO   

SELECT * FROM [dbo].[Foo] ORDER BY [Id] ASC;
SELECT * FROM [dbo].[Foo] ORDER BY CONVERT(VARCHAR(36), [Id]) ASC;
GO

DROP TABLE [dbo].[Foo];
GO

Now what I expected to happen in the first select line is for the data to come back ordered like it is in the second select line. However I notice it is in fact ordered a little differently.

Here is what an order by on the Id column results in:

00000000-0000-0000-0000-000000000000
FFFFFFFF-0000-0000-0000-000000000000
00000000-FFFF-0000-0000-000000000000
00000000-0000-FFFF-0000-000000000000
00000000-0000-0000-FFFF-000000000000
00000000-0000-0000-0000-FFFFFFFFFFFF

 Here is what an order by the string representation of the Id column results in:

00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-FFFFFFFFFFFF
00000000-0000-0000-FFFF-000000000000
00000000-0000-FFFF-0000-000000000000
00000000-FFFF-0000-0000-000000000000
FFFFFFFF-0000-0000-0000-000000000000

Now I wonder why... Anyone know. I am too lazy too look in BOL (aka Google). :P

---

Microsoft Xbox MVP | http://craign.net/

CraigN:

I've always figures SQL Server was intelligent, but today it called me a liar. :P

Now I want to go and hack a server and change one of the system error messages to say just that... 

Now I wonder why... Anyone know. I am too lazy too look in BOL (aka Google). :P

http://blogs.msdn.com/sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx

Basically, byte group on the right hand side it the most significant 'digits' and the byte group on the left the least significant

 

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

Could it be that the first SELECT is sorting at a binary level, while the second one is sorting via ASCII? Especially since the second one is converted to VARCHAR.

My 2c

---

The Question is the Answer, and the Answer is the Question!

My guess would be something to do with binary vs char

If you drop the hyphens then it makes sense because FFFF000000000000 (less leading zeros) is a smaller number than FFFFFFFF000000000000000000000000.

---

Robert MacLean
http://www.sadev.co.za

rmaclean:

If you drop the hyphens then it makes sense because FFFF000000000000 (less leading zeros) is a smaller number than FFFFFFFF000000000000000000000000.

Well normally I'd say that 0x00FF is a smaller value than 0xFF00. Wouldn't you?

Thankfully of SQL Server MVP Gail explained it through the blog post she found.

---

Microsoft Xbox MVP | http://craign.net/

horatio:

Could it be that the first SELECT is sorting at a binary level, while the second one is sorting via ASCII? Especially since the second one is converted to VARCHAR.

My 2c

Kinda, but not quite the way you might expect. The uniqueidentifiers aren't compared as a single entity (like an int would be), but rather the byte blocks (as delimited by the -) are compared individually and in reverse order from what you may expect.

So, if I were to write out the first 16 uniqueidentifiers starting at '0', they would be
00000000-0000-0000-0000-000000000000
00000001-0000-0000-0000-000000000000
00000002-0000-0000-0000-000000000000
00000003-0000-0000-0000-000000000000
00000004-0000-0000-0000-000000000000
00000005-0000-0000-0000-000000000000
00000006-0000-0000-0000-000000000000
00000007-0000-0000-0000-000000000000
00000008-0000-0000-0000-000000000000
00000009-0000-0000-0000-000000000000
0000000A-0000-0000-0000-000000000000
0000000B-0000-0000-0000-000000000000
0000000C-0000-0000-0000-000000000000
0000000D-0000-0000-0000-000000000000
0000000E-0000-0000-0000-000000000000
0000000F-0000-0000-0000-000000000000

If the pattern were continued, the next sequential uniquidentifier after {FFFFFFFF-0000-0000-0000-000000000000} would be {00000000-0001-0000-0000-000000000000}

And so on and so on. Make any sense?

And to make matters more fun, .Net sorts uniquidentifiers in a different way. 

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

Now I want to go and hack a server and change one of the system error messages to say just that... 

Haha! 

---

"That's the thing about people who think they hate computers. What they really hate is lousy programmers."
Greg - Software Development