Readable IDs: A Bounded Computed Invoice Number

Readable IDs can present an invoice number without replacing its numeric database key. A computed column supplies the display format. An explicit width limit prevents quiet truncation when the key grows.

Keep readable IDs separate from the key

The example uses an integer InvoiceId as its primary key. The computed InvoiceNumber adds INV- and six digits. Thus, key 123 displays as INV-000123.

Foreign keys and joins can continue using the integer. The prefix and padding serve readers. They do not add numeric capacity or establish authorization.

Enforce the six-digit limit

RIGHT(...,6) keeps only six characters. Without a bound, 1,000,000 would format misleadingly after truncation. The CHECK accepts keys from 1 through 999,999 and rejects values outside that domain.

This example assigns explicit keys so the boundary is easy to inspect. It does not reseed an identity or change a real invoice table. The computed column is virtual and has no display-number index in this example.

Test readable IDs and their boundary

This needs SQL Server 2012 or later. Run the blocks in order in one query window. They return three display numbers and a lookup for 123. They also test rejection of zero, a negative key and the first seven-digit key.

DROP TABLE IF EXISTS #Invoice;
CREATE TABLE #Invoice
(
    InvoiceId int NOT NULL PRIMARY KEY,
    InvoiceNumber AS CONVERT(varchar(10),
      'INV-' + RIGHT('000000' + CONVERT(varchar(10),InvoiceId),6)),
    Amount decimal(12,2) NOT NULL,
    CHECK (InvoiceId BETWEEN 1 AND 999999)
);
INSERT #Invoice(InvoiceId,Amount)
  VALUES (1,25.00),(123,80.00),(999999,120.00);
SELECT InvoiceId,InvoiceNumber,Amount FROM #Invoice
ORDER BY InvoiceId;

DECLARE @InvoiceNumber varchar(10) = 'INV-000123';
SELECT InvoiceId,InvoiceNumber,Amount FROM #Invoice
WHERE InvoiceNumber = @InvoiceNumber;

Next, try three keys outside the allowed range. Each one should fail, and the table should still hold three rows.

DECLARE @Rejected table (InvoiceId int,ErrorNumber int);
BEGIN TRY INSERT #Invoice(InvoiceId,Amount) VALUES (0,1.00); END TRY
BEGIN CATCH INSERT @Rejected VALUES (0,ERROR_NUMBER()); END CATCH;
BEGIN TRY INSERT #Invoice(InvoiceId,Amount) VALUES (-1,1.00); END TRY
BEGIN CATCH INSERT @Rejected VALUES (-1,ERROR_NUMBER()); END CATCH;
BEGIN TRY INSERT #Invoice(InvoiceId,Amount) VALUES (1000000,1.00); END TRY
BEGIN CATCH INSERT @Rejected VALUES (1000000,ERROR_NUMBER()); END CATCH;
SELECT InvoiceId,ErrorNumber FROM @Rejected ORDER BY InvoiceId;
SELECT COUNT(*) AS AcceptedRows FROM #Invoice;

DROP TABLE #Invoice;

The expected labels are INV-000001, INV-000123 and INV-999999. Each invalid key should fail with constraint error 547. The accepted row count should remain three.

Choose storage and indexing deliberately

A virtual computed column calculates its value when needed. PERSISTED stores a computed value and maintains it when its inputs change. Persistence alone does not make every expression indexable.

An indexed computed column needs a supported deterministic expression and appropriate data types. It also needs six session options set to ON and NUMERIC_ROUNDABORT set to OFF. Check those requirements in creation and application sessions.

FORMAT is nondeterministic and depends on CLR formatting. It does not suit this persisted indexed identifier design. Explicit conversion and padding keep the intended format visible.

This example shows the display rule and its accepted domain, rather than an index-seek performance claim. A production lookup index needs its own representative workload test. Match the lookup parameter to the column’s string type and length.

Plan growth and issued-number rules

Choose a wider format before the six-digit domain runs out. Check printed invoices, imports, reports and saved searches before changing the interface. Preserve previously issued numbers when the business requires immutable document identifiers.

An identity can allocate numeric keys, but it does not guarantee a gap-free issuance sequence. Failed statements and rollbacks can leave gaps. Define a separate controlled issuance process when the business requires one.

Small limits are easier to live with when the table enforces them.

A readable ID is not the key, it is the label readers see.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.