Database Challenges

The following script generates the data required for the performance testing of TSQL Challenge 22.

/*************************************************************
 --------------------------------------------------------------
 Performance Testing Data for TSQL Challenge 22
 Copyright © beyondrelational.com
 -------------------------------------------------------------- 
 
 Notes:
 
 Revision History:
 Rev 00 - 03 June 2010 - Muhammad Al Pasha - Initial Release     
*************************************************************/


-- --------------------------------------------------
-- Create the 'Boxes' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Boxes','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Boxes
END
GO

CREATE TABLE TC22_Boxes
(
    BoxId INT IDENTITY PRIMARY KEY,
    BoxName VARCHAR(20)
)
GO
-- --------------------------------------------------
-- Create the 'Balls' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Balls','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Balls
END
GO

CREATE TABLE TC22_Balls(
    BallId INT IDENTITY PRIMARY KEY,
    BallName VARCHAR(20)
)
GO 
      
-- --------------------------------------------------
-- Create the 'Preferences' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Preferences','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Preferences
END
GO

CREATE TABLE TC22_Preferences(
    PreferenceId INT IDENTITY PRIMARY KEY,
    BoxId INT,
    BallId INT
)
GO

DECLARE @boxes_and_balls_number INT 
SET @boxes_and_balls_number = 10000;
DECLARE @max_offset INT 
SET  @max_offset = 99;

INSERT INTO TC22_Boxes(BoxName)
SELECT 'Box ' + CAST(N AS VARCHAR(7))
FROM tsqlc_Tally
WHERE N >= 1
AND N <= @boxes_and_balls_number;

INSERT INTO TC22_Balls(BallName)
SELECT 'Ball ' + CAST(N AS VARCHAR(7))
FROM tsqlc_Tally
WHERE N >= 1
AND N <= @boxes_and_balls_number;

INSERT INTO TC22_Preferences(BoxID, BallID)
SELECT 1 AS BoxID, 1 AS BallID
UNION ALL
SELECT Bo.BoxId, Ba.BallId + 1
FROM TC22_Boxes AS Bo
INNER JOIN TC22_Balls AS Ba ON Ba.BallId <= Bo.BoxId + @max_offset
WHERE Ba.BallId % Bo.BoxId = Bo.BoxId - 1
AND  BallID + 1 <= @boxes_and_balls_number 
ORDER BY BoxID