Database Challenges

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

/*************************************************************
 --------------------------------------------------------------
 Performance Testing Data for TSQL Challenge 21
 Copyright © beyondrelational.com
 -------------------------------------------------------------- 
 
 Notes:
 
 Revision History:
 Rev 00 - 20 May 2010 - Jacob Sebastian	  - Initial Release
 Rev 01 - 24 May 2010 - Muhammad Al Pasha - re-wrote the script
     
*************************************************************/

-- If the table exists, drop it.
IF OBJECT_ID('dbo.TC21_Data','U') IS NOT NULL BEGIN
	DROP TABLE TC21_Data;
END
   
-- Create the destination Table
CREATE TABLE dbo.TC21_Data(
	AutoID INT IDENTITY, 
	ID     INT, 
	ScanNumber NVARCHAR(250)
);
WITH NumbersStrings(number, number_string) AS
(
   SELECT CAST(N'0' AS CHAR(1)), CAST(N' _ | ||_|' AS CHAR(9)) UNION ALL
   SELECT CAST(N'1' AS CHAR(1)), CAST(N'     |  |' AS CHAR(9)) UNION ALL
   SELECT CAST(N'2' AS CHAR(1)), CAST(N' _  _||_ ' AS CHAR(9)) UNION ALL
   SELECT CAST(N'3' AS CHAR(1)), CAST(N' _  _| _|' AS CHAR(9)) UNION ALL
   SELECT CAST(N'4' AS CHAR(1)), CAST(N'   |_|  |' AS CHAR(9)) UNION ALL
   SELECT CAST(N'5' AS CHAR(1)), CAST(N' _ |_  _|' AS CHAR(9)) UNION ALL
   SELECT CAST(N'6' AS CHAR(1)), CAST(N' _ |_ |_|' AS CHAR(9)) UNION ALL
   SELECT CAST(N'7' AS CHAR(1)), CAST(N' _   |  |' AS CHAR(9)) UNION ALL
   SELECT CAST(N'8' AS CHAR(1)), CAST(N' _ |_||_|' AS CHAR(9)) UNION ALL
   SELECT CAST(N'9' AS CHAR(1)), CAST(N' _ |_| _|' AS CHAR(9))
)
INSERT INTO TC21_Data(ID, ScanNumber)
   SELECT TOP(10000) CAST(N1.number + N2.number + N3.number + N4.number + N5.number +
                          N6.number + N7.number + N8.number + N9.number AS INT),
          SUBSTRING(N1.number_string, 1, 3) + SUBSTRING(N2.number_string, 1, 3) +
             SUBSTRING(N3.number_string, 1, 3) + SUBSTRING(N4.number_string, 1, 3) +
             SUBSTRING(N5.number_string, 1, 3) + SUBSTRING(N6.number_string, 1, 3) +
             SUBSTRING(N7.number_string, 1, 3) + SUBSTRING(N8.number_string, 1, 3) +
             SUBSTRING(N9.number_string, 1, 3) +  CHAR(13) + CHAR(10) +
          SUBSTRING(N1.number_string, 4, 3) + SUBSTRING(N2.number_string, 4, 3) +
             SUBSTRING(N3.number_string, 4, 3) + SUBSTRING(N4.number_string, 4, 3) +
             SUBSTRING(N5.number_string, 4, 3) + SUBSTRING(N6.number_string, 4, 3) +
             SUBSTRING(N7.number_string, 4, 3) + SUBSTRING(N8.number_string, 4, 3) +
             SUBSTRING(N9.number_string, 4, 3) +  CHAR(13) + CHAR(10) +
          SUBSTRING(N1.number_string, 7, 3) + SUBSTRING(N2.number_string, 7, 3) +
             SUBSTRING(N3.number_string, 7, 3) + SUBSTRING(N4.number_string, 7, 3) +
             SUBSTRING(N5.number_string, 7, 3) + SUBSTRING(N6.number_string, 7, 3) +
             SUBSTRING(N7.number_string, 7, 3) + SUBSTRING(N8.number_string, 7, 3) +
             SUBSTRING(N9.number_string, 7, 3) +  CHAR(13) + CHAR(10)
     FROM NumbersStrings AS N1, NumbersStrings AS N2, NumbersStrings AS N3,
          NumbersStrings AS N4, NumbersStrings AS N5, NumbersStrings AS N6,
          NumbersStrings AS N7, NumbersStrings AS N8, NumbersStrings AS N9
    WHERE CAST(N1.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N2.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N3.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N4.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N5.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N6.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N7.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N8.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4)
      AND CAST(N9.number AS INT) BETWEEN (ABS(CHECKSUM(NEWID())) % 4) AND 6 + (ABS(CHECKSUM(NEWID())) % 4);