We have a number of tables in our database that contain 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 So how does SQL Anywhere 12.0.1.3895 sort asked 11 Jul '13, 10:12 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 answered 11 Jul '13, 11:13 Breck Carter 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
That might be different in a while - thanks to Breck - and to your question:)
(12 Jul '13, 08:38)
Volker Barth
|
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?
Actually, our system architect asked the question. I figured it was worth asking here. ;)
"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.
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.