Generating Unique, Hard-to-Guess Voucher Codes in T-SQL

Good voucher codes come from random bytes, a clear alphabet, and a primary key that refuses duplicates. SQL Server gives you CRYPT_GEN_RANDOM for the bytes. The rest is a few careful choices. Let me build 20 codes and check them.

Why a code that looks random can be guessed

Picture a promotion with codes like SAVE2026 and SAVE2027. A script tries a few hundred of them overnight, and by morning someone has free orders. The codes looked fine. They were just easy to guess.

RAND is not meant for this, and neither is NEWID, which makes an identifier and not a secret. For a voucher, ask SQL Server for random bytes with CRYPT_GEN_RANDOM. Then turn those bytes into characters from an alphabet people can read over the phone.

My alphabet has 32 symbols: the digits 2 to 9 and the letters A to Z, except I and O. No zero, no one, no mix-ups. A 16-character code from 32 symbols has 32 to the power of 16 combinations. The query shows that number, which is 1,208,925,819,614,629,174,706,176, or about 1.2 septillion. Nobody guesses their way through that.

DECLARE @Alphabet varchar(32) = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';

SELECT LEN(@Alphabet) AS AlphabetLength,
       POWER(CAST(LEN(@Alphabet) AS decimal(38, 0)), 16) AS Combinations;

Why the alphabet size is 32

A random byte is a number from 0 to 255. To pick a symbol, I take the byte modulo 32. That works because 256 divides evenly by 32, so every symbol is equally likely. With an alphabet of 30 symbols, it would not divide evenly, and some symbols would come up more often.

You can see this by counting how many of the 256 byte values land on each symbol.

SELECT s.Symbols,
       COUNT(*)   AS Buckets,
       MIN(b.Hits) AS MinBytesPerSymbol,
       MAX(b.Hits) AS MaxBytesPerSymbol
FROM (VALUES (32), (30)) AS s (Symbols)
CROSS APPLY (SELECT value % s.Symbols AS Bucket, COUNT(*) AS Hits
             FROM GENERATE_SERIES(0, 255)
             GROUP BY value % s.Symbols) AS b
GROUP BY s.Symbols
ORDER BY s.Symbols DESC;

With 32 symbols, every symbol gets exactly 8 byte values. With 30, some get 8 and some get 9, so a few symbols are slightly more likely. That is a small bias, but it is free to avoid. Do not copy this mapping to an arbitrary alphabet without checking it.

Generate the codes with a retry limit

The table stores the code, an expiry date, and a redemption time. The code column uses a binary collation, so A and a are different, and the primary key blocks duplicates.

The loop builds one code at a time from 16 random bytes. It skips a code that already exists and tries again. The limit of 1,000 attempts matters. If something is wrong, the loop stops with an error instead of spinning forever.

DROP TABLE IF EXISTS #Vouchers;

CREATE TABLE #Vouchers
(
    Code        varchar(16) COLLATE Latin1_General_100_BIN2 PRIMARY KEY,
    ExpiresAt   datetime2 NOT NULL DEFAULT DATEADD(DAY, 30, SYSUTCDATETIME()),
    RedeemedAt  datetime2 NULL
);

DECLARE @Alphabet varchar(32) = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
DECLARE @Attempts int = 0, @Bytes varbinary(16), @Code varchar(16), @Position int;

SET NOCOUNT ON;
WHILE (SELECT COUNT(*) FROM #Vouchers) < 20 AND @Attempts < 1000
BEGIN
    SELECT @Bytes = CRYPT_GEN_RANDOM(16), @Code = '', @Position = 1;
    WHILE @Position <= 16
    BEGIN
        SET @Code += SUBSTRING(@Alphabet, CONVERT(int, SUBSTRING(@Bytes, @Position, 1)) % 32 + 1, 1);
        SET @Position += 1;
    END;
    IF NOT EXISTS (SELECT 1 FROM #Vouchers WHERE Code = @Code)
        INSERT #Vouchers (Code) VALUES (@Code);
    SET @Attempts += 1;
END;

SET NOCOUNT OFF;

IF (SELECT COUNT(*) FROM #Vouchers) < 20 THROW 50001, 'Retry limit reached.', 1;

Check what you generated

Never assume the codes are right. Count them, check the lengths, and look for any character outside the alphabet. The last query shows the first five codes in a friendlier form, with hyphens added only for display. Store the plain code, and strip hyphens from whatever a customer types before you look it up.

SELECT COUNT(*) AS Codes, MIN(LEN(Code)) AS MinimumLength, MAX(LEN(Code)) AS MaximumLength
FROM #Vouchers;

SELECT COUNT(*) AS InvalidCodes
FROM #Vouchers
WHERE Code LIKE '%[^23456789ABCDEFGHJKLMNPQRSTUVWXYZ]%' COLLATE Latin1_General_100_BIN2;

SELECT TOP (5) Code,
       CONCAT(LEFT(Code, 4), '-', SUBSTRING(Code, 5, 4), '-',
              SUBSTRING(Code, 9, 4), '-', RIGHT(Code, 4)) AS DisplayCode
FROM #Vouchers
ORDER BY Code;

You should see 20 codes, all 16 characters long, and zero invalid ones. The codes themselves change on every run, so yours will not match mine.

Protect the code when it is redeemed

A strong code is not enough. A stolen or shared code works until you stop it. So each code needs an expiry, a single-use rule, and an atomic redemption. One UPDATE that checks and marks the code in the same statement does all three. Run it twice for the same code.

DECLARE @Code varchar(16) = (SELECT TOP (1) Code FROM #Vouchers ORDER BY Code);

UPDATE #Vouchers
SET RedeemedAt = SYSUTCDATETIME()
WHERE Code = @Code AND RedeemedAt IS NULL AND ExpiresAt > SYSUTCDATETIME();
SELECT @@ROWCOUNT AS FirstTry;

UPDATE #Vouchers
SET RedeemedAt = SYSUTCDATETIME()
WHERE Code = @Code AND RedeemedAt IS NULL AND ExpiresAt > SYSUTCDATETIME();
SELECT @@ROWCOUNT AS SecondTry;

The first try changes one row. The second changes none, because the code is already used. Two customers cannot both win, even at the same moment. In your application, also limit failed guesses per user or address, and avoid logging live codes. The last block drops the temp table.

DROP TABLE IF EXISTS #Vouchers;

Make the code hard to guess, then make it hard to reuse.

A voucher code is not just random text, it is a secret that must survive redemption.

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