Database Challenges

Here is the data we used for the performance testing of TSQL Challenge 17

/***********************************************************************
      Purpose: Test data generator for TSQL Challenge 17 - Load Testing
         Date: 26 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.TC17','U') IS NOT NULL
    BEGIN
          RAISERROR('RUN ABORTED. "dbo.TC17" already exists.',11,1);
          RAISERROR('Please drop the table if you wish to recreate.',10,1);
          RETURN;
      END;

       IF OBJECT_ID('dbo.TC17_TMP','U') IS NOT NULL
    BEGIN
          RAISERROR('RUN ABORTED. "dbo.TC17_TMP" 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.TC17(
          id          INTEGER IDENTITY, 
          keyword     VARCHAR(36), 
          data        VARCHAR(500),
          CONSTRAINT PK_TC17
          PRIMARY KEY (id));


--===== Create a temporary table

   CREATE TABLE dbo.TC17_TMP(
          id          INTEGER, 
          keyword     VARCHAR(36), 
          nb1         INTEGER,
          nb2         INTEGER,
          CONSTRAINT PK_TC17_TMP
          PRIMARY KEY (id));

--===== Populate the temporary table with Guids, and "random" numbers

     WITH CTEBig (id, keyword) AS(
   SELECT 1, REPLACE(NEWID(),'-','')
    UNION ALL
   SELECT id + 1, REPLACE(NEWID(),'-','')
     FROM CTEBig
    WHERE id < 250)
  
        , CTE1 (id, keyword, nb1, nb2) AS(
   SELECT k1.id, CAST(k1.keyword as VARCHAR(36)),
          (k1.id + CAST(FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() OVER(ORDER BY (SELECT 0))) AS VARCHAR(36)), 3) AS FLOAT)) AS INTEGER))%250,
          ROUND(FLOOR(CAST(RIGHT(CAST(RAND(ROW_NUMBER() OVER(ORDER BY (SELECT 0))) AS VARCHAR(36)), 3) AS FLOAT))*3/1000, 0)
     FROM CTEBig AS k1)

   INSERT INTO dbo.TC17_TMP (id, keyword, nb1, nb2)
   SELECT id, keyword, nb1, nb2
     FROM CTE1
   OPTION (MAXRECURSION 32767);
  
--===== Populate the destination table with list of guids

     WITH CTE2 (id, keyword, data) AS(
   SELECT t1.id, t1.keyword, t2.keyword
     FROM dbo.TC17_TMP AS t1
          INNER JOIN dbo.TC17_TMP AS t2
            ON t2.id BETWEEN t1.nb1 - t1.nb2 AND t1.nb1 + t1.nb2
    WHERE t2.id <> t1.id)

   INSERT INTO dbo.TC17 (keyword, data)
   SELECT t1.keyword,
          STUFF( (SELECT ' ' + CAST(t2.data AS VARCHAR(36))
                    FROM CTE2 AS t2
                   WHERE t2.id = t1.id
                     FOR XML PATH('')), 1, 1, '')
     FROM CTE2 AS t1
 GROUP BY t1.id, t1.keyword;