-- Sample table

DECLARE @Question

TABLE (

question_id INTEGER IDENTITY PRIMARY KEY,

quiz_id INTEGER NOT NULL,

title NVARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

answer_1 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

answer_2 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

answer_3 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

answer_4 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

correct_answer TINYINT NOT NULL CHECK (correct_answer BETWEEN 1 AND 4)

);

-- Add 50,000 rows of randon test data

WITH

-- Number generator

L0 AS (SELECT 1 AS C UNION ALL SELECT 1),

L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS (SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),

Numbers

AS

(

SELECT TOP (50000)

ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N

FROM L4

ORDER BY N

)

-- Random data generator

INSERT @Question

(quiz_id, title, answer_1, answer_2, answer_3, answer_4, correct_answer)

SELECT ABS(CHECKSUM(NEWID())) % 10 + 1,

N'title #' + CONVERT(NVARCHAR(2), ABS(CHECKSUM(NEWID())) % 99 + 1),

CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

ABS(CHECKSUM(NEWID())) % 4 + 1

FROM Numbers

-- This is the quiz_id we want questions for

DECLARE @QuizID INTEGER;

SET @QuizID = 5;

-- 40 random questions

SELECT TOP (40)

Q.title,

Q.answer_1,

Q.answer_2,

Q.answer_3,

Q.answer_4,

Q.correct_answer

FROM @Question Q

WHERE Q.quiz_id = @QuizID

ORDER BY

NEWID()

OPTION (RECOMPILE);