SQL Server GUID sort algorithm. Why?

by

The algorithm is documented by the SQL Server guys here: How are GUIDs compared in SQL Server 2005? I Quote here here (since it’s an old article that may be gone forever in a few years)

In general, equality comparisons make a lot of sense with
uniqueidentifier values. However, if you find yourself needing general
ordering, then you might be looking at the wrong data type and should
consider various integer types instead.

If, after careful thought, you decide to order on a uniqueidentifier
column, you might be surprised by what you get back.

Given these two uniqueidentifier values:

@g1= ‘55666BEE-B3A0-4BF5-81A7-86FF976E763F’ @g2 =
‘8DD5BCA5-6ABE-4F73-B4B7-393AE6BBB849’

Many people think that @g1 is less than @g2, since ‘55666BEE’ is
certainly smaller than ‘8DD5BCA5’. However, this is not how SQL Server
2005 compares uniqueidentifier values.

The comparison is made by looking at byte “groups” right-to-left, and
left-to-right within a byte “group”. A byte group is what is delimited
by the ‘-‘ character. More technically, we look at bytes {10 to 15}
first, then {8-9}, then {6-7}, then {4-5}, and lastly {0 to 3}.

In this specific example, we would start by comparing ’86FF976E763F’
with ‘393AE6BBB849’. Immediately we see that @g2 is indeed greater
than @g1.

Note that in .NET languages, Guid values have a different default sort
order than in SQL Server. If you find the need to order an array or
list of Guid using SQL Server comparison semantics, you can use an
array or list of SqlGuid instead, which implements IComparable in a
way which is consistent with SQL Server semantics.

Plus, the sort follows byte groups endianness (see here: Globally unique identifier). The groups 10-15 and 8-9 are stored as big endian (corresponding to the Data4 in the wikipedia article), so they are compared as big endian. Other groups are compared using little endian.

More Related Contents:

  1. SQL Server query to find all permissions/access for all users in a database
  2. Natural (human alpha-numeric) sort in Microsoft SQL 2005
  3. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  4. Why use the INCLUDE clause when creating an index?
  5. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  6. Is it possible to force row level locking in SQL Server?
  7. Regular Expressions in SQL Server servers?
  8. What happens to the primary key Id when it goes over the limit?
  9. Drop primary key using script in SQL Server database [duplicate]
  10. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  11. SQL Server recursive query
  12. nvarchar(max) vs NText
  13. INFORMATION_SCHEMA vs sysobjects
  14. “Prevent saving changes that require the table to be re-created” negative effects
  15. What are row, page and table locks? And when they are acquired?
  16. Table Valued Function Killing My Query Performance
  17. Export from SQL Server to Excel
  18. What is the best way to create and populate a numbers table?
  19. Infinite loop CTE with OPTION (maxrecursion 0)
  20. SQL Server IN vs. EXISTS Performance
  21. How Can I Log and Find the Most Expensive Queries?
  22. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  23. PIVOT in sql 2005
  24. Atomic UPSERT in SQL Server 2005
  25. SQL Server ORDER BY date and nulls last
  26. How to update primary key
  27. error when inserting into table having instead of trigger from entity data framework
  28. Case sensitive variables in SQL Server
  29. How to query for Xml values and attributes from table in SQL Server?
  30. How to find the boundaries of groups of contiguous sequential numbers?

Leave a Comment