Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL UUIDs

Reference for storing UUIDs in SQL: native UUID types, text versus 16-byte binary, generating values in each database and the cost of random keys.

A UUID (universally unique identifier) is a 128-bit value, written as 32 hexadecimal digits in five groups, for example 123e4567-e89b-12d3-a456-426614174000. Systems generate UUIDs independently without asking a central counter, so two servers, or a mobile app that is offline, can create ids that will not collide. That makes them popular as primary keys, public identifiers and idempotency keys.

Support differs. PostgreSQL and (via UNIQUEIDENTIFIER) SQL Server have a native type. MySQL, Oracle and SQLite do not, so you store a UUID as 36-character text or as 16 bytes of binary. SQLite has no UUID function at all; you build one from random bytes.

SQL
CREATE TABLE sessions (
  id         TEXT PRIMARY KEY,   -- or BLOB for 16 bytes
  created_at TEXT NOT NULL
);

Try it

SQL UUID text versus 16-byte binary

Press Run (or Ctrl + Enter) to see the result here.

SQL Generating a random UUID shape in SQLite

Press Run (or Ctrl + Enter) to see the result here.

The values differ on every run, but the shape does not: 32 hex digits become 36 characters once the four hyphens are inserted. This example builds random bytes only; it does not set the version and variant bits that mark an RFC 4122 version 4 UUID, so use a library or a database function for real identifiers.

Syntax

SQL
column_name UUID                       -- PostgreSQL
column_name UNIQUEIDENTIFIER           -- SQL Server
column_name BINARY(16)                 -- MySQL
column_name BLOB                       -- SQLite (16 bytes)
column_name TEXT                       -- any database (36 characters)
Form Size Notes
Native type 16 bytes Compact, validated, displayed in the hyphenated form.
BINARY(16) / BLOB 16 bytes Compact, but you convert to and from text in queries.
Text (CHAR(36)) 36+ bytes Readable, about twice the size, and costs more index space.

Dialect differences

Database Native type Generate a random UUID Notes
SQLite None Build from randomblob(16) There is no built-in uuid() function.
PostgreSQL UUID gen_random_uuid() (13+) PostgreSQL 18 adds uuidv7() for time-ordered ids.
MySQL None (BINARY(16)) UUID() UUID_TO_BIN(x, 1) swaps time fields so ids sort roughly by time; BIN_TO_UUID() reverses it.
SQL Server UNIQUEIDENTIFIER NEWID() NEWSEQUENTIALID() as a column default gives increasing values.
Oracle None (RAW(16)) SYS_GUID() Returns 16 raw bytes.
SQL
-- PostgreSQL
CREATE TABLE sessions (id UUID PRIMARY KEY DEFAULT gen_random_uuid());

-- MySQL
CREATE TABLE sessions (id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)));

-- SQL Server
CREATE TABLE sessions (id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID());

Usage notes

  • Random keys fragment indexes. A fully random version 4 UUID inserts into random places of a B-tree, which causes page splits and cache misses on large tables. Time-ordered variants (UUIDv7, MySQL's swapped form, NEWSEQUENTIALID()) keep new rows near each other.
  • Size matters. The key is repeated in every secondary index and every foreign key that points to it, so 16 bytes versus 36 characters adds up.
  • They do not replace ordering. A version 4 UUID tells you nothing about creation time. Keep a created_at column.
  • Not secret by default. Time-based versions leak creation time, and a UUID is not an access token unless it comes from a secure random source.
  • Compare in one form. Store lowercase hyphenated text or binary, not both; 'ABC...' and 'abc...' are different strings.
  • When an integer is enough, a plain auto-increment key is smaller and faster. Use UUIDs when ids are created outside the database or must not be guessable in URLs.
  • Validate the text form with a CHECK (for example length(id) = 36) when the database has no native type.