Solved

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

Posted on 2005-04-25
433 Views
If you run a query like:

SELECT * FROM <table> ORDER BY RowGUID

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.
0
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:

'368FCFC6-6486-455B-BA6C-FF5C467FDF78'

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

Thus, this is 'Larger'

'368FCFC6-6486-455B-0000-FF5C467FDF79'

and this is 'Smaller'

'368FCFC6-6486-FFFF-FFFF-FF5C467FDF77'

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

'368FCFC6-6486-FFFF-AAAA-FF5C467FDF78'

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

'368FCFC6-6486-FF5A-BA6C-FF5C467FDF78'

(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
0

LVL 10

Expert Comment

From BOL
<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.
</BOL>

/Richard
0

LVL 10

Expert Comment

or you could try Binary_Checksum

/Richard
0

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!
0

## Featured Post

### Suggested Solutions

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.

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!