A random selection is easy to run and harder to explain afterward. Picking random winners fairly means freezing the eligible entries, choosing the rule, and keeping evidence of the draw.
Freeze the Eligible Population First
Random ordering cannot fix an unfair input list. Define eligibility, duplicate handling, and whether one person can receive more than one selection. Store one row per eligible entrant when the rule permits only one selection per person. Keep the approved weights separate from that identity.
I check duplicate identities before discussing the random function. Two rows for one person give that person another chance, whether anybody intended it or not. A wonderfully random query can still favor a wonderfully duplicated input.
This teaching table uses invented identifiers and weights. Run the examples in a scratch database and the same SSMS session. The check constraint accepts positive integer weights. The primary key prevents duplicate entrant rows. Real eligibility needs an approved snapshot rather than a changing query over live signups.
CREATE TABLE #Entrants
(
EntrantID int NOT NULL PRIMARY KEY,
IsEligible bit NOT NULL,
EntryWeight int NOT NULL CHECK (EntryWeight > 0)
);
INSERT #Entrants VALUES
(10, 1, 1), (20, 1, 1), (30, 1, 3), (40, 1, 2), (50, 0, 1);
SELECT EntrantID, EntryWeight
FROM #Entrants WHERE IsEligible = 1 ORDER BY EntrantID;NEWID Picks Random Winners With a Practical Shuffle
ORDER BY NEWID assigns random GUID ordering to the eligible rows. TOP selects the requested number from that ordering. With one row per entrant, selecting several rows does not repeat an entrant. The query is a practical equal-entry shuffle, not an auditable record by itself.
The engine must process and sort the eligible population. A small draw is straightforward. A very large one needs measured resource planning. Also verify that the requested count does not exceed eligibility. TOP silently returns fewer rows when the input contains fewer eligible entrants.
Do you need equal chances or approved weighted chances? Decide before running anything. The following query ignores EntryWeight deliberately. Mixing a weight column into the input does not make NEWID honor it automatically.
DECLARE @Requested int = 3;
IF @Requested <= 0 OR @Requested >
(SELECT COUNT(*) FROM #Entrants WHERE IsEligible = 1)
THROW 50000, 'Choose a valid count of eligible entrants.', 1;
SELECT TOP (@Requested) EntrantID
FROM #Entrants
WHERE IsEligible = 1
ORDER BY NEWID();RAND Is Not a Row-by-Row Shuffle
A plain RAND() expression in this SELECT produces the same statement-level value across rows. Sorting by that shared value gives no useful random ranking. The query below exposes the problem beside NEWID. Do not mistake RAND's valid number for independent randomness per entrant.
A seeded RAND produces a repeatable pseudorandom sequence under controlled calls. It is not a cryptographic fairness mechanism. Repeatedly reseeding or inventing a seed expression without understanding evaluation can produce another misleading shuffle. Keep the method simple enough to explain and test.
SELECT EntrantID, RAND() AS SharedRandomValue, NEWID() AS RowRandomValue
FROM #Entrants
WHERE IsEligible = 1;Use Cryptographic Bytes for Unpredictable Seeds
CRYPT_GEN_RANDOM supplies cryptographic random bytes. The next block generates a four-byte value and maps its nonnegative portion to a number strictly between zero and one. Masking avoids the overflow trap caused by ABS on the minimum signed integer.
For the saved draw later, generate a thirty-two-byte seed instead. The seed is an input to a documented deterministic ranking method. Do not assume the optional seed argument of CRYPT_GEN_RANDOM guarantees replayable output. Replay must follow your specified algorithm and frozen input.
A secret seed helps prevent advance prediction, but secrecy alone does not prevent an operator from drawing repeatedly. Publish or independently retain the chosen seed commitment and eligibility snapshot according to the process. Once the draw finishes, keep the seed and selected result together.
DECLARE @RandomBytes varbinary(4) = CRYPT_GEN_RANDOM(4);
DECLARE @Nonnegative int = CONVERT(int, @RandomBytes) & 2147483647;
SELECT @RandomBytes AS RandomBytes,
(@Nonnegative + 1.0) / 2147483649.0 AS UnitValue;
SELECT CRYPT_GEN_RANDOM(32) AS ExampleDrawSeed;
Weighted Random Winners Without Repeats
Expanding one person into several tickets gives that person more chances. It also creates repeated-person selections unless the selection process removes that person's remaining tickets after a win. Simple TOP over expanded tickets therefore does not satisfy every multiple-selection rule.
A weighted race offers one score per entrant. Generate a uniform value between zero and one, then compute minus LOG(value) divided by the positive weight. Choose the smallest scores. This implements successive weighted selection without replacement under the underlying uniform model.
The demonstration derives values from a fresh cryptographic seed and each fixed-width identifier. Its finite numeric representation introduces discretization, and exact score ties use EntrantID. For high-stakes draws, specify and independently review that complete algorithm. Do not describe an undocumented floating-point shortcut as a mathematical guarantee.
DECLARE @WeightSeed varbinary(32) = CRYPT_GEN_RANDOM(32);
DROP TABLE IF EXISTS #WeightedScores;
SELECT EntrantID, EntryWeight,
CONVERT(float,
(CONVERT(int, SUBSTRING(HASHBYTES('SHA2_256',
@WeightSeed + CONVERT(binary(4), EntrantID)), 1, 4)) & 2147483647)
+ 1.0) / 2147483649.0 AS UnitValue
INTO #WeightedScores
FROM #Entrants
WHERE IsEligible = 1;
SELECT TOP (3) EntrantID, EntryWeight,
-LOG(UnitValue) / EntryWeight AS RaceScore
FROM #WeightedScores
ORDER BY RaceScore, EntrantID;Save the Population, Seed, and Timestamp
The persistent example below records an equal-entry draw, separate from the weighted demonstration. Its method name is UniformHashV1. It ranks a SHA2_256 digest of the seed plus each four-byte integer identifier. Saving the method version makes the byte representation part of the record.
Create these tables once in the scratch database. Keep the header and all eligible entries, not only the selected identities. Their saved ranks reveal the ordering that produced the result. In production, protect this evidence from casual modification and define who can authorize a redraw.
CREATE TABLE dbo.DrawHeader
(
DrawID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
DrawnUtc datetime2(7) NOT NULL,
DrawSeed varbinary(32) NOT NULL,
MethodName varchar(40) NOT NULL,
RequestedCount int NOT NULL
);
CREATE TABLE dbo.DrawEntries
(
DrawID int NOT NULL,
EntrantID int NOT NULL,
EntryWeight int NOT NULL,
ScoreBytes varbinary(32) NOT NULL,
DrawRank bigint NOT NULL,
IsSelected bit NOT NULL,
PRIMARY KEY (DrawID, EntrantID),
UNIQUE (DrawID, DrawRank),
FOREIGN KEY (DrawID) REFERENCES dbo.DrawHeader (DrawID)
);Record One Draw as One Transaction
Validate the requested count first. Then record the header and frozen entry ranks together. A failure rolls back both. The sample's input is a session-owned temporary table, so another session cannot change its population midway through this example.
A production draw over shared data needs a stable approved snapshot. Include its provenance in your record. Generating a seed after approving that snapshot prevents picking a seed to favor a newly altered population. Independent oversight also limits repeated hidden attempts before saving an attractive result.
DECLARE @Wanted int = 3;
IF @Wanted <= 0 OR @Wanted >
(SELECT COUNT(*) FROM #Entrants WHERE IsEligible = 1)
THROW 50001, 'The saved draw needs a valid selection count.', 1;
DECLARE @DrawSeed varbinary(32) = CRYPT_GEN_RANDOM(32);
DECLARE @DrawID int;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
INSERT dbo.DrawHeader
(DrawnUtc, DrawSeed, MethodName, RequestedCount)
VALUES (SYSUTCDATETIME(), @DrawSeed, 'UniformHashV1', @Wanted);
SET @DrawID = CONVERT(int, SCOPE_IDENTITY());
;WITH Scores AS
(
SELECT EntrantID, EntryWeight,
HASHBYTES('SHA2_256', @DrawSeed + CONVERT(binary(4), EntrantID)) AS ScoreBytes
FROM #Entrants WHERE IsEligible = 1
), Ranked AS
(
SELECT EntrantID, EntryWeight, ScoreBytes,
ROW_NUMBER() OVER (ORDER BY ScoreBytes, EntrantID) AS DrawRank
FROM Scores
)
INSERT dbo.DrawEntries
(DrawID, EntrantID, EntryWeight, ScoreBytes, DrawRank, IsSelected)
SELECT @DrawID, EntrantID, EntryWeight, ScoreBytes, DrawRank,
CASE WHEN DrawRank <= @Wanted THEN 1 ELSE 0 END
FROM Ranked;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
SELECT h.DrawID, h.DrawnUtc, h.DrawSeed, h.MethodName,
e.EntrantID, e.DrawRank
FROM dbo.DrawHeader AS h
JOIN dbo.DrawEntries AS e ON e.DrawID = h.DrawID
WHERE h.DrawID = @DrawID AND e.IsSelected = 1
ORDER BY e.DrawRank;Explain Random Winners From Retained Evidence
I retain the whole ranking whenever somebody needs to defend a selection. The original population, method version, seed, and timestamp explain what ran. A seed without the exact input and algorithm is incomplete evidence. A screenshot of the winners is weaker still.
To verify the saved example, recompute each digest from the retained seed and identifier bytes. Compare it with ScoreBytes, then sort by that digest and EntrantID. The resulting ranks should match the saved ranks. Perform that check against the frozen entries, not a newly queried population.
For random winners, define redraw conditions before the first attempt. Keep failed or voided draws according to that policy rather than erasing inconvenient results. Random winners become defensible when eligibility and operator behavior receive the same care as the random function.
Related reading on this blog: Generating Random Numbers Per Row: RAND, NEWID and CRYPT_GEN_RANDOM and Techniques for Retrieving Random Rows.
A fair draw is not just a random order, it is a selection process you can explain afterward.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
