Building a Lucky Draw System Using SQL Server – SQL Circuit

Building a Lucky Draw System Using SQL Server

Want to randomly pick a winner from a list of participants? Whether you’re running an office contest, event giveaway, or an online reward program, a Lucky Draw System can be easily built using SQL Server—no fancy tools or coding required!

Why SQL Server for a Lucky Draw?

  • Quick and easy implementation using existing SQL skills
  • Built-in randomness with NEWID()
  • Ideal for one-time or recurring draws
  • No external apps or scripts needed

Step 1: Create a Pool of Ticket Number

Let’s create a table filled with ticket numbers from 1 to 100,000, representing eligible entries:

-- Create the table
CREATE TABLE dbo.LuckyDrawPool (
    TicketNumber INT PRIMARY KEY
);


-- Populate with numbers from 1 to 100000
WITH Numbers AS (
    SELECT 1 AS TicketNumber
    UNION ALL
    SELECT TicketNumber + 1 FROM Numbers WHERE TicketNumber < 100000
)
INSERT INTO dbo.LuckyDrawPool (TicketNumber)
SELECT TicketNumber FROM Numbers
OPTION (MAXRECURSION 0);

Step 2: Drawing a Random Winner

Use the NEWID() function in SQL Server to shuffle the entries and select a random ticket number:

-- Select a random winner
SELECT TOP 1 TicketNumber, 
GETDATE() AS [Timestamp]
FROM LuckyDrawPool
ORDER BY NEWID();

Every time this query runs, it returns a new random entry—just like a digital Lucky Draw!

Step 3: Enhance the System

How to get multiple Winners (Top 5 Winners)

SELECT TOP 5 TicketNumber , 
GETDATE() aS [Timestamp]
FROM dbo.LuckyDrawPool
ORDER BY NEWID();

Track Past Winners

CREATE TABLE dbo.LuckyDrawWinners (
    WinnerID INT PRIMARY KEY IDENTITY,
    TicketNumber INT,
    WinDate DATETIME DEFAULT GETDATE()
);


-- Save current winner
INSERT INTO dbo.LuckyDrawWinners (TicketNumber)
SELECT TOP 1 TicketNumber FROM dbo.LuckyDrawPool ORDER BY NEWID();

Prevent Repeat Winners

SELECT TOP 1 TicketNumber
FROM dbo.LuckyDrawPool
WHERE TicketNumber NOT IN (SELECT TicketNumber FROM LuckyDrawWinners)
ORDER BY NEWID();

Use Case(s)

  • Employee recognition and bonus programs
  • Event-based giveaways and marketing contests
  • Random sampling for QA testing or analytics
  • Simulating randomness in data modeling pipelines
  • Online Contest Winners
  • Gamification in dashboards

Leave a Reply

Your email address will not be published. Required fields are marked *