Listing 1: Creating and Populating Table T1 in testdb

SET NOCOUNT ON;
IF DB_ID('testdb') IS NULL
  CREATE DATABASE testdb;
GO
USE testdb;
GO

-- Create the function fn_split
IF OBJECT_ID('dbo.fn_nums', 'IF') IS NOT NULL
  DROP FUNCTION dbo.fn_nums;
GO

CREATE FUNCTION dbo.fn_nums(@n AS INT)
   RETURNS TABLE AS RETURN

WITH
C0 AS(SELECT 0 AS const UNION ALL SELECT 0),
C1 AS(SELECT 0 AS const FROM C0 AS A, C0 AS B),
C2 AS(SELECT 0 AS const FROM C1 AS A, C1 AS B),
C3 AS(SELECT 0 AS const FROM C2 AS A, C2 AS B),
C4 AS(SELECT 0 AS const FROM C3 AS A, C3 AS B),
C5 AS(SELECT 0 AS const FROM C4 AS A, C4 AS B),
C6 AS(SELECT 0 AS const FROM C5 AS A, C5 AS B)
SELECT TOP(@n) ROW_NUMBER() OVER
   (ORDER BY const) AS n
FROM C6;
GO

-- Create the table T1
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  filler CHAR(200) NOT NULL
);

CREATE CLUSTERED INDEX
   idx_cl_col1 ON dbo.T1(col1);
CREATE NONCLUSTERED INDEX
   idx_nc_col2 ON dbo.T1(col2);
GO

-- Populate T1 with 1,000,000 rows
DECLARE @numrows AS INT;
SET @numrows = 1000000;

TRUNCATE TABLE dbo.T1;

INSERT INTO dbo.T1(col1, col2, filler)
  SELECT n, 1 + ABS(CHECKSUM(n))
      % 1000000000, 'a'
  FROM dbo.fn_nums(@numrows);
GO