Binary Primary Key

[jms.c...@gmail.com]

Hi,

I would like some insight/opinions on using binary types for primary
keys - one assumption could be that these keys would be the binary
representation of a UUID. A second assumption could be that UUID data
types are not supported by a database.

Thanks.

[Andreas Henningsson]

I would stick to INT, SMALLINT, BIGINT for primary keys. Depending on the numbers

or rows in the table I need to store. 

/Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
Andreas Henningsson

"Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire

[Jorge Cercas]

Hi Andreas,

Lets assume, for argument sake, that the data types you mentioned just wouldn't meet the specified requirements.

[Kerry Sainsbury]

Binary data types as keys would make using database tools for adhoc queries practically impossible. That alone is enough to throw out the idea for me.

In my world primary keys should always be meaningless, so an INT should always be just fine. Your binary fields don't sound meaningless to me, so I'd never use them as keys.

I'm keen to hear your "requirements" though.

Kerry

[Thomas Mueller]

Hi,

H2 does support the UUID data type, and there is no "technical"
problem using UUIDs for primary keys. The only potential problem is
performance.

If you have an index on randomly generated data (and by default UUIDs
are randomly generated), then read and write performance for this
index will drop significantly once the data is too big to fit in the
cache. The reason is that locality (where the data is stored) of
randomly generated data is by definition very bad, so cache efficiency
is bad. This doesn't just affect UUIDs, the problem would be the same
if you have an index on randomly generated data of any type. To work
around this problem, you could use sequential UUIDs. I'm not sure yet
how you can do that, but I would be interested in such a generator
(does anybody know what would be the best way?). See also:

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
http://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid
http://stackoverflow.com/questions/45399/advantages-and-disadvantages-of-guid-uuid-database-keys

See also http://www.h2database.com/html/performance.html#database_performance_tuning
"How Data is Stored Internally".

Regards,
Thomas