Database Challenges

Here is the data we used for the load testing of TSQL Challenge 16

/***********************************************************************
      Purpose: Test data generator for TSQL Challenge 16 - Load Testing
         Date: 12 Feb 2010
 Submitted BY: Fabien "Waldar" Contaminard
  Profile URL: "http://beyondrelational.com/members/Waldar/default.aspx"
***********************************************************************/

--===== If the tables already exist, something is wrong.
     -- Warn/instruct the operator and exit early.
     IF OBJECT_ID('dbo.TC16','U') IS NOT NULL
  BEGIN
        RAISERROR('RUN ABORTED. "dbo.TC16" already exists.',11,1);
        RAISERROR('Please drop the table if you wish to recreate.',10,1);
        RETURN;
    END;
    
--===== Create the destination table
 CREATE TABLE dbo.TC16(
		PersonID	int, 
		Surname		nvarchar(16), 
		FirstName	nvarchar(16), 
		Description nvarchar(16), 
		StartDate	datetime, 
		EndDate		datetime);


--===== Create tables for temp processing
DECLARE @Member TABLE(
		PersonID	int identity, 
		Surname		nvarchar(16), 
		FirstName	nvarchar(16));

DECLARE @Activity TABLE(
		Description nvarchar(16));

--===== Create 500 random people
   WITH CTE1k (nm) AS(
 SELECT 1
  UNION ALL
 SELECT nm + 1 FROM CTE1k
  WHERE nm < 500 )
  
 INSERT INTO @Member(Surname, FirstName)
 SELECT 'Surname' + CAST(nm AS VARCHAR(3)),
        'FirstName' + CAST(nm AS VARCHAR(3))
   FROM CTE1k
 OPTION (MAXRECURSION 1000);

--===== Create 100 random activities
   WITH CTE1k (nm) AS (
 SELECT 1
  UNION ALL
 SELECT nm + 1 
   FROM CTE1k
  WHERE nm < 100)
  
 INSERT INTO @Activity
 SELECT 'Activity' + CAST(nm AS VARCHAR(3))
   FROM CTE1k
 OPTION (MAXRECURSION 1000);

--===== Populate the destination table
 INSERT INTO dbo.TC16(
        PersonID, 
        Surname, 
        FirstName, 
        Description, 
        StartDate, 
        EndDate )
 SELECT M.PersonID, 
		M.Surname, 
		M.FirstName, 
		A.Description,
		CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -
			CASE
				WHEN FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() 
						OVER(ORDER BY (SELECT 0))) AS VARCHAR), 3) AS FLOAT)) > 
				     FLOOR(CAST(RIGHT(CAST(RAND(1000000-ROW_NUMBER() 
						OVER(ORDER BY (SELECT 1))) AS VARCHAR), 3) AS FLOAT))
				THEN FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() 
					 OVER(ORDER BY (SELECT 0))) AS VARCHAR), 3) AS FLOAT))
				ELSE FLOOR(CAST(RIGHT(CAST(RAND(1000000-ROW_NUMBER() 
					 OVER(ORDER BY (SELECT 1))) AS VARCHAR), 3) AS FLOAT))
			END,
			CAST(FLOOR(CAST(GETDATE() AS FLOAT)) as datetime) -
			NULLIF(
			CASE
				WHEN FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() 
						OVER(ORDER BY (SELECT 0))) AS VARCHAR), 3) AS FLOAT)) > 
				     FLOOR(CAST(RIGHT(CAST(RAND(100000-ROW_NUMBER() 
						OVER(ORDER BY (SELECT 1))) AS VARCHAR), 3) AS FLOAT))
				THEN FLOOR(CAST(RIGHT(CAST(RAND(100000-ROW_NUMBER() 
					 OVER(ORDER BY (SELECT 1))) AS VARCHAR), 3) AS FLOAT))
				ELSE FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() 
					 OVER(ORDER BY (SELECT 0))) AS VARCHAR), 3) AS FLOAT))
			END, 
			CASE
				WHEN FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() 
						OVER(ORDER BY (SELECT 0))) AS VARCHAR), 2) AS FLOAT)) > 
					 FLOOR(CAST(RIGHT(CAST(RAND(100000-ROW_NUMBER() 
						OVER(ORDER BY (SELECT 1))) AS VARCHAR), 2) AS FLOAT))
				THEN FLOOR(CAST(RIGHT(CAST(RAND(100000-ROW_NUMBER() 
					 OVER(ORDER BY (SELECT 1))) AS VARCHAR), 2) AS FLOAT))
				ELSE FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() 
					 OVER(ORDER BY (SELECT 0))) AS VARCHAR), 2) AS FLOAT))
			END)
   FROM @Member AS M
  CROSS JOIN @Activity AS A;