Database Challenges

Here is the data we used for the Load Testing of TSQL Challenge #15. The solutions were tested using a large table with 50,000 rows, having 25,000 negative numbers and 25,000 positive numbers. The script given below uses the TSQL Challenge Tally Table for generating the data needed for the testing. If you have not created this table yet, you can find the script here.

/***********************************************************************
 Purpose:
 Test data generator for TSQL Challenge 15 - Load Testing
***********************************************************************/
--===== If the test tables already exist, something is wrong.
     -- Warn/instruct the operator and exit early.
     IF OBJECT_ID('dbo.TC15_Cols','U') IS NOT NULL
  BEGIN
        RAISERROR('RUN ABORTED. "dbo.TC15_Cols" already exists.',11,1);
        RAISERROR('Please drop the table if you wish to recreate.',10,1);
        RETURN;
    END;

     IF OBJECT_ID('dbo.TC15_Rows','U') IS NOT NULL
  BEGIN
        RAISERROR('RUN ABORTED. "dbo.TC15_Rows" already exists.',11,1);
        RAISERROR('Please drop the table if you wish to recreate.',10,1);
        RETURN;
    END;

--===== Assign the max absolute value for testing
     -- and the "offset" to make it come out right.
DECLARE @MaxAbsValue INT,
        @OffSet      INT

 SELECT @MaxAbsValue = 25000, -- Change this number only
        @OffSet      = @MaxAbsValue + 1

--===== Create and populate the 'Cols' table
 SELECT ISNULL(t.N, 0) AS Col
   INTO dbo.TC15_Cols
   FROM dbo.TSQLC_Tally t
  WHERE t.N BETWEEN 1 AND 9;

--===== Create and populate the 'Rows' table
     -- with equal numbers of both positive
     -- and negative values excluding zero.
 SELECT ISNULL(t.N - @OffSet, 0) AS Row
   INTO dbo.TC15_Rows
   FROM dbo.TSQLC_Tally t
  WHERE t.N BETWEEN 1 AND @MaxAbsValue + @OffSet
    AND t.N <> @OffSet;

--===== Add clustered indexes to the test tables in the form of PK's
  ALTER TABLE dbo.TC15_Cols
    ADD CONSTRAINT PK_TC15_Cols_Col
        PRIMARY KEY CLUSTERED (Col)

  ALTER TABLE dbo.TC15_Rows
    ADD CONSTRAINT PK_TC15_Rows_Row
        PRIMARY KEY CLUSTERED (Row)

--===== Display the contents of both tables
 SELECT * FROM dbo.TC15_Cols c ORDER BY Col;
 SELECT * FROM dbo.TC15_Rows r ORDER BY Row;