Running a Contest with SQL Server

If you ever need to run a random drawing contest with a SQL Server database back end, here’s one way to do it:

Create a table to hold each person’s name and email address.  We use EntryID as a primary key, not their name or email, because a person can enter more than once.

CREATE TABLE [dbo].[Entry](
    [EntryID] [int] IDENTITY(1,1) NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [EmailAddress] [varchar](100) NOT NULL,
    [PrizeWon] [varchar](50) NULL,
 CONSTRAINT [PK_Entry] PRIMARY KEY CLUSTERED
(
    [EntryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert a record each time somebody enters:

INSERT INTO [dbo].[Entry] ([FullName], [EmailAddress])
  VALUES ('Hugo Reyes', 'big-money@no-whammies.com')

Draw a random winner by running this script for each prize:

DECLARE @WinningEntryID INT
SET @WinningEntryID = (SELECT TOP 1 [EntryID]
  FROM [dbo].[Entry]
  WHERE [PrizeWon] IS NULL
  ORDER BY NEWID())
UPDATE [dbo].[Entry]
  SET [PrizeWon] = 'Pound of Bacon'
  WHERE [EntryID] = @WinningEntryID

The “order by newid()” part will give us a random record out of the table.  The “where PrizeWon is null” part will make sure we don’t draw an entry again after it’s already won.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts