Question Re: How does SQL Anywhere sort Guids / UniqueIdentifiers in Technology Q&A

How does SQL Anywhere sort Guids / UniqueIdentifiers

Former Member — Thu, 11 Jul 2013 10:12:44 GMT

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?

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Breck_Carter — Thu, 11 Jul 2013 10:45:51 GMT

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?

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Former Member — Thu, 11 Jul 2013 10:47:39 GMT

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

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Breck_Carter — Thu, 11 Jul 2013 11:13:05 GMT

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

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Breck_Carter — Thu, 11 Jul 2013 11:16:35 GMT

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

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Breck_Carter — Thu, 11 Jul 2013 11:20:25 GMT

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

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Former Member — Fri, 12 Jul 2013 08:30:52 GMT

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.

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

Former Member — Fri, 12 Jul 2013 08:37:59 GMT

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.

Re: How does SQL Anywhere sort Guids / UniqueIdentifiers

VolkerBarth — Fri, 12 Jul 2013 08:38:51 GMT

a Google search came back empty.

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