How does SQL Anywhere sort Guids / UniqueIdentifiers

[Former Member]

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?

[Breck_Carter]

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?

[Former Member]

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

[Breck_Carter]

"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.

[Former Member]

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.

[Breck_Carter · Accepted solution]

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 

[Breck_Carter · Accepted solution]

...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?

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

a Google search came back empty.

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