Share via

How are GUIDs compared in SQL Server 2005?

  • Article

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.

Comments

  • Anonymous
    November 11, 2006
    Je viens de prendre conscience de cette fameuse différence en lisant le post How are GUIDs compared in

  • Anonymous
    August 31, 2007
    Today I ran into a nice problem with SQL Server fancy way of sorting GUIDS. I know, there should be no

  • Anonymous
    April 24, 2008
    A few months ago we started development on a new system. From the ground up we redesigned everything

  • Anonymous
    October 26, 2008
    Unraveling the mysteries of NewSequentialID