Common UUID Mistakes Developers Make (and How to Avoid Them)

UUIDs look deceptively simple: call a function, get back 36 characters, move on. In practice, they're one of the most frequently misused primitives in modern software — misused in ways that don't show up in local development, only in production, at scale, six months after launch. This post walks through the mistakes that recur across codebases and database schemas, why each one hurts, and the concrete fix.

---

1. Using Random UUIDs (v4) as a Clustered Primary Key

This is the single most common — and most expensive — UUID mistake in relational databases.

The mechanics

A UUIDv4 is ~122 bits of cryptographically random data. When you use it as a clustered index key (the default for a SQL Server PRIMARY KEY, or an InnoDB table in MySQL/MariaDB), every single insert has to land at a random position in the B-tree, not at the end.

That causes:

• Page splits — pages fill up out of order, forcing the engine to split and rebalance nodes constantly.
• Poor cache locality — the "hot" working set of pages the database needs in memory keeps growing because inserts scatter across the whole index instead of hitting the last page.
• Index bloat and fragmentation — measurable, ongoing fragmentation that requires regular REBUILD/REORGANIZE maintenance.

The fix: use a time-ordered UUID, or don't cluster on the UUID at all

-- Bad: UUIDv4 as clustered PK in SQL Server
CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, -- random, fragments badly
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

-- Better: sequential GUIDs for the clustering key
CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY, -- monotonic-ish
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

// Better still: generate UUIDv7 in application code (time-ordered, RFC 9562 compliant)
using System;

Guid orderId = Guid.CreateVersion7(); // .NET 9+, RFC 9562 UUIDv7

If you're on an older .NET runtime without native CreateVersion7, use a library (e.g. UUIDNext) rather than hand-rolling the bit layout — getting the version/variant nibbles wrong (see mistake #5) is easy to do manually.

Performance comparison

Rule of thumb: if the UUID is your clustering key, it needs to be time-ordered. If you want pure random UUIDs for external-facing IDs, keep them as a non-clustered UNIQUE column and cluster on something else (an IDENTITY/BIGINT, or a UUIDv7).

---

2. Storing UUIDs as VARCHAR(36) Instead of a Native/Binary Type

The mechanics

A UUID is 128 bits — 16 bytes. Stored as a hyphenated string ("550e8400-e29b-41d4-a716-446655440000"), it becomes a 36-character string, which at minimum consumes 36 bytes and often more depending on collation and encoding (e.g. NVARCHAR doubles it to 72 bytes).

That's a 2.25x–4.5x storage penalty, multiplied across every row and every index that includes the column — and string comparison for joins/sorts is slower than fixed-width binary comparison.

The fix

-- Bad
CREATE TABLE Users (
    Id VARCHAR(36) PRIMARY KEY,
    Email NVARCHAR(255)
);

-- Good (SQL Server)
CREATE TABLE Users (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Email NVARCHAR(255)
);

-- Good (MySQL/Postgres — no native GUID type in MySQL, so use BINARY(16))
CREATE TABLE Users (
    Id BINARY(16) PRIMARY KEY,
    Email VARCHAR(255)
);

Only convert to the string form at the API/presentation boundary, never at the storage layer.

---

3. Assuming String Sort Order Matches Binary Sort Order

The mechanics

This one is subtle and SQL Server-specific, but it trips up developers who assume ORDER BY UniqueIdentifierColumn behaves like sorting the string representation. SQL Server's UNIQUEIDENTIFIER sorts by comparing byte groups in a non-intuitive order — the last 6 bytes are compared before some of the earlier bytes, due to legacy Windows GUID byte-swapping (little-endian storage of the first three fields).

This means a UUIDv7's embedded timestamp, which sorts correctly as a plain byte array or as a BINARY(16) in MySQL/Postgres, does not sort chronologically when stored as a SQL Server UNIQUEIDENTIFIER and ordered naively.

-- This does NOT reliably return rows in creation order for UUIDv7 values
-- stored as UNIQUEIDENTIFIER in SQL Server:
SELECT * FROM Orders ORDER BY Id;

The fix

• On MySQL/Postgres (BINARY(16)/BYTEA), byte-array comparison matches UUIDv7's intended chronological order — no special handling needed.
• On SQL Server, either store the UUID's raw bytes in the exact big-endian order it was generated in and compare via CONVERT(BINARY(16), Id) semantics carefully, or — simpler — keep a separate CreatedAt DATETIME2 column and sort by that instead of relying on GUID byte ordering.

// Don't rely on Guid.CompareTo() for chronological ordering either —
// .NET's Guid comparison follows the same SQL Server-style field ordering,
// not a straight byte-array comparison.

---

4. Treating UUIDs as a Substitute for Authorization

The mechanics

A common pattern: GET /api/invoices/{uuid} with no additional access check, on the theory that "you can't guess a UUID." This is security by obscurity, not authorization. UUIDs can leak through:

• Browser history and referrer headers
• Server logs and error trackers (Sentry, Application Insights)
• Shared links, screenshots, browser extensions
• Timing/side-channel attacks in poorly implemented systems

An unguessable ID is not the same as an authorized request.

The fix

// Bad
[HttpGet("invoices/{id}")]
public IActionResult GetInvoice(Guid id)
{
    var invoice = _db.Invoices.Find(id);
    return Ok(invoice); // no ownership check!
}

// Good
[HttpGet("invoices/{id}")]
public IActionResult GetInvoice(Guid id)
{
    var invoice = _db.Invoices.Find(id);
    if (invoice is null || invoice.OwnerId != CurrentUser.Id)
        return Forbid();

    return Ok(invoice);
}

Use UUIDs to avoid enumeration attacks (sequential IDs let attackers scrape /orders/1, /orders/2, ...), but never as the sole access-control mechanism.

---

5. Hand-Rolling UUID Generation and Getting the Version/Variant Bits Wrong

The mechanics

A valid RFC 9562 UUID reserves specific bits:

• 4 bits for the version (bits 48–51 of the 128-bit layout)
• 2 bits for the variant (top 2 bits of the 65th bit, forced to 10)

Developers occasionally build "UUID-like" identifiers by concatenating a timestamp and random bytes manually — without setting these fields — producing a value that looks like a UUID but fails validation in libraries that check the version/variant, or worse, silently collides with the reserved value ranges other systems assume are safe (e.g., assuming version 4 means "fully random" when your hand-rolled value isn't).

// Bad: hand-rolled, non-compliant "UUID"
function fakeUuid() {
  const hex = () => Math.floor(Math.random() * 16).toString(16);
  return Array.from({ length: 32 }, hex).join('').replace(
    /(.{8})(.{4})(.{4})(.{4})(.{12})/, '$1-$2-$3-$4-$5'
  );
  // no version nibble set, no variant bits set, Math.random() is not
  // cryptographically secure either — see mistake #6
}

The fix

Always use a spec-compliant library or built-in:

// Good — Node.js 19+ / modern browsers
const id = crypto.randomUUID(); // RFC 9562 UUIDv4, correct bits, CSPRNG-backed

// Good — .NET
Guid id = Guid.NewGuid();       // UUIDv4
Guid id7 = Guid.CreateVersion7(); // UUIDv7, .NET 9+

Never construct UUID byte layouts by hand unless you are implementing UUIDv8 for a genuinely custom, documented format — and even then, set the version/variant nibbles explicitly per spec.

---

6. Using a Weak Random Source

The mechanics

UUIDv4's collision resistance depends entirely on the quality of its randomness. Math.random() in JavaScript is not cryptographically secure — it's a fast PRNG with a small internal state, not designed to resist prediction. Using it to generate identifiers that need to be unguessable (session tokens, password reset links, API keys formatted as UUIDs) is a real vulnerability, not a theoretical one.

The fix

Always use your platform's built-in, CSPRNG-backed UUID generator. Never build one on top of a general-purpose PRNG.

---

7. Comparing UUIDs Case-Sensitively (or Inconsistently)

The mechanics

RFC 9562 UUIDs are canonically lowercase, but plenty of systems emit uppercase (ToString("D").ToUpper(), some legacy Windows APIs, some JSON serializers). If your application stores one casing and compares against another using a case-sensitive collation or string equality check, "identical" UUIDs will silently fail to match.

// Bug: case mismatch silently fails
string incoming = "550E8400-E29B-41D4-A716-446655440000";
string stored   = "550e8400-e29b-41d4-a716-446655440000";

bool matches = incoming == stored; // false! but they're the same UUID

The fix

// Compare as Guid, not as string — Guid.Equals is case-insensitive by design
bool matches = Guid.Parse(incoming) == Guid.Parse(stored); // true

-- If you must store as a string, normalize on write and use a
-- case-insensitive collation, or better, use a native GUID/BINARY type
-- so the question doesn't arise at all.

---

8. Ignoring Clock Behavior in Time-Based Versions (v1, v6, v7)

The mechanics

Time-based UUID versions embed a timestamp. If the underlying clock jumps backward (NTP correction, VM migration, container restart with a skewed clock) or if you generate a burst of UUIDs within the same millisecond, naive implementations can produce values that are out of order or, in the worst case for v1/v6 with poorly implemented clock sequences, collide.

RFC 9562 addresses this for UUIDv7 by allowing implementations to use sub-millisecond precision or a monotonic counter in the random bits, but not every library does this correctly.

The fix

• Prefer a well-maintained, spec-compliant library for UUIDv7 generation (or your runtime's built-in, e.g. Guid.CreateVersion7() in .NET 9+) rather than a hand-rolled implementation.
• Don't assume strict global monotonicity across distributed nodes with unsynchronized clocks — UUIDv7 gives you approximate time-ordering (good enough for index locality), not a guaranteed distributed sequence number. If you need a hard ordering guarantee, use a dedicated sequence generator (e.g. Snowflake-style IDs) instead.

---

Comparing the Alternatives

---

Summary

Most UUID mistakes come from treating UUIDs as an opaque, interchangeable "unique string" rather than a structured 128-bit value with real performance and security characteristics attached to how it's generated and stored:

1. Match the UUID version to the job — v4 for opacity, v7 for database-friendly ordering.
2. Store UUIDs in their native/binary form, not as padded strings.
3. Don't assume string sort order equals chronological order, especially on SQL Server.
4. UUIDs are not an authorization mechanism.
5. Never hand-roll UUID generation — use spec-compliant, CSPRNG-backed libraries.
6. Compare UUIDs as typed values, not raw strings, to avoid casing bugs.
7. Understand that time-based versions offer approximate, not guaranteed, global ordering.

None of these are exotic edge cases — they're the mistakes that show up in code review, in production incident reports, and in "why is our index so fragmented" tickets across almost every team that adopts UUIDs without reading the spec first.