How to compare 2 RowGUIDs in the same way that "ORDER BY RowGUID" does

Posted on 2005-04-25
Last Modified: 2011-10-03
If you run a query like:


you'll find that the result set is not sorted by the alphabetical representation of the RowGUIDs.  I've read that the sort is based on the binary representation of the data in the database.  No problem, but I need to do a comparison of two RowGUID that uses the same criteria that SQL Server does.  Neither a memcmp on the UUID structure itself nor UuidCompare seems to work.
Question by:BSWatts
    LVL 32

    Accepted Solution

    The comparison algorithm is -- weird, to say the least.

    I have investigated this a bit, and SQL Server comparisons work like this:

    Given this GUID:


    First, compare by the final 6 bytes (12 characters)

    Thus, this is 'Larger'


    and this is 'Smaller'


    Then, the next 2 bytes (4 characters) back, so this is smaller:


    Then, the next individual byte (2 characters), then the one after that, so this is smaller:


    (Is this weird enough yet?)

    If you actually are identical through that much code, you can work out the rest of the rules yourself.  A simple query to use to test is:

    Declare @x uniqueidentifier
    Declare @y uniqueidentifier

    Set @x = '368FCFC6-6486-455B-BA6C-FF5C467FDF78'
    Set @y = '368FCFC6-6486-455B-BA6C-FF5C467FDF78'

    Select @x,':', @y
    Select Case WHEN @x > @y Then 1 When @x < @y Then -1 Else 0 END as XGtY

    Change things one byte at a time, and in combinations, to work out the weird patterns here.

    Now, to compare by this, you will have to reorganize the data.  A quick sample query:

    Declare @v1 varchar(48)
    Declare @v2 varchar(48)

    Set @v1 = cast(newid() as Varchar(48))
    Set @v2 = cast(newid() as Varchar(48))

    Declare @Compare1 varchar(48)
    Declare @Compare2 varchar(48)

    Set @Compare1 = Right(@V1, 12) + substring(@v1, 20,4) + Substring(@v1, 17, 2) + Substring(@v1, 15, 2)
    Set @Compare2 = Right(@v2, 12) + substring(@v2, 20,4) + Substring(@v2, 17, 2) + Substring(@v1, 15, 2)
    Select Case WHEN @Compare1 > @Compare2 THEN 1 WHEN @Compare1 < @Compare2 Then -1 ELSE 0 END
    LVL 10

    Expert Comment

    From BOL
    Comparison operators can be used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that are allowed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators are allowed. All column constraints and properties except IDENTITY are allowed on the uniqueidentifier data type.

    LVL 10

    Expert Comment

    or you could try Binary_Checksum


    Author Comment

    You may both be right - I should have mentioned that I'm doing this from C++, so I think bhess1's info will be the most useful.  Thanks to you both!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now