Bringing Uniqueness with a CTE Sequence

Uniqueness is something we like to reinforce within data. Sometimes the uniqueness will mean we reject data, but other times we want to make it unique.

Let’s consider an account name or an order reference. We wouldn’t want to simply reject an account or an order, we want to ensure they’re unique.

A typical approach is to add a numeric suffix onto the reference to make it unique. If Andy exists we might want Andy1, if that exists try Andy2, but how far do we go?

Below we solve this with a recursive CTE to generate a unique reference number for each customer order:

WITH Suffixes AS (
	SELECT Suffix = CAST('' AS VARCHAR(5)),
        Ctr = 0
	UNION ALL
	SELECT Suffix = '-' + CAST(s.Ctr + 1 AS VARCHAR(4)),
        Ctr = s.Ctr + 1
	FROM Suffixes s
	WHERE s.Ctr < 1000
)
INSERT INTO dbo.OrderHeader (
	CustomerID,
	OrderRef,
	OrderDate
)
SELECT
	@CustomerID,
	q.OrderRef,
	@OrderDate
FROM (
	SELECT TOP (1) OrderRef = (@OrderRef + s.Suffix)
	FROM Suffixes s
		LEFT JOIN dbo.OrderHeader h
			ON h.CustomerID = @CustomerID
			AND h.OrderRef = (@OrderRef + s.Suffix)
	WHERE h.OrderID IS NULL
	ORDER BY s.Ctr
	) q
OPTION (MAXRECURSION 0);

Let’s break this down:

The Suffixes CTE which generates 1001 suffixes to try, the first being an empty string (we’d prefer no suffix) followed by a 1000 entries with a hyphen and number. There’s also a counter to limit the recursion and enforce the priority order.

Selection of the new reference is via a sub-query with a TOP (1) clause to pick the first entry which doesn’t exist. This is based on a join to the OrderHeader using the reference plus the suffix, and the WHERE clause filters headers which already exist with that reference. The ORDER BY uses the counter above to enforce the suffix order.

The suffix can be customised as the order of preference for them is based on the counter. The suffix could could be numeric, or just as easily be text, a random number, or anything you fancy.

That’s all there is to it, a CTE and a subquery to snatch uniqueness from the jaws of duplication!