Sql-server – What’s the most efficient UUID column type

indexMySQLoraclepostgresqlsql server

For storing a 128 bits UUID there are multiple storage options:

  1. a byte[16] column
  2. two bigint/long(64 bits) columns
  3. a CHAR(36) column – 32 hex digits + 4 dashes.
  4. a UUID database specific column, if db supports it

From an indexing point of view which of those are the most efficient? If the db doesn't support a dedicated uuid type which of 1, 2, 3 are the best candidates?

Best Answer

  • A dedicated uuid type is your best bet for PostgreSQL. Hard to say with other DBs - it's not impossible for someone to impliment a uuid type that's stored less efficiently than a simple byte type.

    Again in PostgreSQL, bytea would be a reasonable way to store UUIDs if you didn't have the uuid type. For other DBs it depends on how they store binary data.

    Where possible I'd strongly avoid using hex-with-dashes. It's way less efficient to compare, sort, and store.

    So really, "not (2) or (3)". Ever. Use (4) where supported, (1) otherwise.