[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

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

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
BSWatts
Asked:
BSWatts
  • 2
1 Solution
 
Brendt HessSenior DBACommented:
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
 
RichardCorrieCommented:
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
 
RichardCorrieCommented:
or you could try Binary_Checksum

/Richard
0
 
BSWattsAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now