We have a number of tables in our database that contain UNIQUEIDENTIFIER columns. The question has arisen as to exactly how these are sorted, especially if you add an index on such a column. In particular, how does the sorting of this type differ, if at all, from how C# might sort them.

The documentation says that these values are stored as BINARY(16). I guess that they would be sorted by taking the 16 bytes in the order they appear in memory / disk and comparing them as a string of bytes. But is the order of the bytes in the BINARY(16) the same as the order in which the digits appear when you convert the UNIQUEIDENTIFIER to a string? My understanding is .NET does not store the bytes in that order, so they don't sort in that order, either.

So how does SQL Anywhere 12.0.1.3895 sort UNIQUEIDENTIFIERS?

asked 11 Jul '13, 10:12

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 11 Jul '13, 10:31

You ask the d*****est questions!

Let's add a third question, along with "How does SQL Anywhere sort GUIDs?" and "How does .NET sort GUIDs?"

Danger! Will Robinson! Danger!

How does SQL Server sort GUIDs?

(11 Jul '13, 10:45) Breck Carter
Replies hidden

Actually, our system architect asked the question. I figured it was worth asking here. ;)

(11 Jul '13, 10:47) TonyV

"worth asking" - oh yes, as Alberto Ferrari pointed out in that link, sorting-for-comparison-purposes is an important process.

As far as indexes are concerned, I imagine UNIQUEIDENTIFIER is a pretty good random hash, if that's what you want for your index; i.e., good for equality searches, sucks for range queries.

(11 Jul '13, 11:20) Breck Carter

About the only thing a UNIQUEIDENTIFIER is good for is uniquely identifying a record. So what use is a range query with a UNIQUEIDENTIFIER key anyway?

Anyway, thanks again for the answer.

(12 Jul '13, 08:37) TonyV

In 12.0.1.3298 columns with the UNIQUEIDENTIFIER data type are sorted by ORDER BY just like character strings.

Under the hood, they may be treated as BINARY but the actual data only contains 0-9 and a-e so the values might as well be VARCHAR... no collation or character set worries, methinks.

Here's proof...

CREATE TABLE t ( 
    pkey   INTEGER          NOT NULL PRIMARY KEY,
    guid   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    string VARCHAR ( 36 )   COMPUTE ( UUIDTOSTR ( guid ) ) );

INSERT t ( pkey ) SELECT row_num FROM sa_rowgenerator ( 1, 10 );
COMMIT;

SELECT pkey, guid FROM t ORDER BY guid;
SELECT pkey, string FROM t ORDER BY string;

pkey        guid                                   
----------- -------------------------------------- 
2           0x2a353f246c5c43948bd0f969db3dc53b     
1           0x3547a2aa04d442b2b523f8f4da468c80     
3           0x4d99bbd3aaf64fb6ae3699050927d0ab     
6           0x55530a30c4204ddfb56a367b4ee81026     
10          0x5b3d2eab6aa64ce3bc3bc640ac4ed579     
7           0x5ccf0d35616e464e8992ba0ac773cf1b     
8           0xb540fe6d9b15495db321202083a9e17a     
4           0xe03acc02cc5846daa713866354befbdb     
9           0xe9ab069d0cd84302ad54a72812694a4b     
5           0xef875058e8274d099c57e93c297c1902

pkey        string                               
----------- ------------------------------------ 
2           2a353f24-6c5c-4394-8bd0-f969db3dc53b 
1           3547a2aa-04d4-42b2-b523-f8f4da468c80 
3           4d99bbd3-aaf6-4fb6-ae36-99050927d0ab 
6           55530a30-c420-4ddf-b56a-367b4ee81026 
10          5b3d2eab-6aa6-4ce3-bc3b-c640ac4ed579 
7           5ccf0d35-616e-464e-8992-ba0ac773cf1b 
8           b540fe6d-9b15-495d-b321-202083a9e17a 
4           e03acc02-cc58-46da-a713-866354befbdb 
9           e9ab069d-0cd8-4302-ad54-a72812694a4b 
5           ef875058-e827-4d09-9c57-e93c297c1902 
permanent link

answered 11 Jul '13, 11:13

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

1

...an excellent example of the Watcom Rule. What possible reason could there be for different behavior? Clearly, Microsoft engineers have way too much time on their hands... they invented ODBC, what more proof must they provide?

(11 Jul '13, 11:16) Breck Carter

Breck...Thanks for the answer. I really wasn't expecting anything different, but I couldn't find anything in the documentation about it, and a Google search came back empty.

(12 Jul '13, 08:30) TonyV
Replies hidden

a Google search came back empty.

That might be different in a while - thanks to Breck - and to your question:)

(12 Jul '13, 08:38) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×7
×3

question asked: 11 Jul '13, 10:12

question was seen: 4,105 times

last updated: 12 Jul '13, 08:38