1 SET NOCOUNT ON; 2 USE master; 3 GO 4 IF DB_ID('Performance') IS NULL 5 CREATE DATABASE Performance; 6 GO 7 USE Performance; 8 GO 9 10 -- Creating and Populating the Nums Auxiliary Table 11 IF OBJECT_ID('dbo.Nums') IS NOT NULL 12 DROP TABLE dbo.Nums; 13 GO 14 CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); 15 DECLARE @max AS INT, @rc AS INT; 16 SET @max = 1000000; 17 SET @rc = 1; 18 19 INSERT INTO Nums VALUES(1); 20 WHILE @rc * 2 <= @max 21 BEGIN 22 INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; 23 SET @rc = @rc * 2; 24 END 25 26 INSERT INTO dbo.Nums 27 SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max; 28 GO 29 30 -- Drop Data Tables if Exist 31 IF OBJECT_ID('dbo.Orders') IS NOT NULL 32 DROP TABLE dbo.Orders; 33 GO 34 IF OBJECT_ID('dbo.Customers') IS NOT NULL 35 DROP TABLE dbo.Customers; 36 GO 37 IF OBJECT_ID('dbo.Employees') IS NOT NULL 38 DROP TABLE dbo.Employees; 39 GO 40 IF OBJECT_ID('dbo.Shippers') IS NOT NULL 41 DROP TABLE dbo.Shippers; 42 GO 43 44 -- Data Distribution Settings 45 DECLARE 46 @numorders AS INT, 47 @numcusts AS INT, 48 @numemps AS INT, 49 @numshippers AS INT, 50 @numyears AS INT, 51 @startdate AS DATETIME; 52 53 SELECT 54 @numorders = 1000000, 55 @numcusts = 20000, 56 @numemps = 500, 57 @numshippers = 5, 58 @numyears = 4, 59 @startdate = '20030101'; 60 61 -- Creating and Populating the Customers Table 62 CREATE TABLE dbo.Customers 63 ( 64 custid CHAR(11) NOT NULL, 65 custname NVARCHAR(50) NOT NULL 66 ); 67 68 INSERT INTO dbo.Customers(custid, custname) 69 SELECT 70 'C' + RIGHT('000000000' + CAST(n AS VARCHAR(10)), 10) AS custid, 71 N'Cust_' + CAST(n AS VARCHAR(10)) AS custname 72 FROM dbo.Nums 73 WHERE n <= @numcusts; 74 75 ALTER TABLE dbo.Customers ADD 76 CONSTRAINT PK_Customers PRIMARY KEY(custid); 77 78 -- Creating and Populating the Employees Table 79 CREATE TABLE dbo.Employees 80 ( 81 empid INT NOT NULL, 82 firstname NVARCHAR(25) NOT NULL, 83 lastname NVARCHAR(25) NOT NULL 84 ); 85 86 INSERT INTO dbo.Employees(empid, firstname, lastname) 87 SELECT n AS empid, 88 N'Fname_' + CAST(n AS NVARCHAR(10)) AS firstname, 89 N'Lname_' + CAST(n AS NVARCHAR(10)) AS lastname 90 FROM dbo.Nums 91 WHERE n <= @numemps; 92 93 ALTER TABLE dbo.Employees ADD 94 CONSTRAINT PK_Employees PRIMARY KEY(empid); 95 96 -- Creating and Populating the Shippers Table 97 CREATE TABLE dbo.Shippers 98 ( 99 shipperid VARCHAR(5) NOT NULL, 100 shippername NVARCHAR(50) NOT NULL 101 ); 102 INSERT INTO dbo.Shippers(shipperid, shippername) 103 SELECT shipperid, N'Shipper_' + shipperid AS shippername 104 FROM (SELECT CHAR(ASCII('A') - 2 + 2 * n) AS shipperid 105 FROM dbo.Nums 106 WHERE n <= @numshippers) AS D; 107 108 ALTER TABLE dbo.Shippers ADD 109 CONSTRAINT PK_Shippers PRIMARY KEY(shipperid); 110 111 -- Creating and Populating the Orders Table 112 CREATE TABLE dbo.Orders 113 ( 114 orderid INT NOT NULL, 115 custid CHAR(11) NOT NULL, 116 empid INT NOT NULL, 117 shipperid VARCHAR(5) NOT NULL, 118 orderdate DATETIME NOT NULL, 119 filler CHAR(155) NOT NULL DEFAULT('a') 120 ); 121 122 INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate) 123 SELECT n AS orderid, 124 'C' + RIGHT('000000000' 125 + CAST( 126 1 + ABS(CHECKSUM(NEWID())) % @numcusts 127 AS VARCHAR(10)), 10) AS custid, 128 1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid, 129 CHAR(ASCII('A') - 2 130 + 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid, 131 DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate) 132 -- late arrival with earlier date 133 - CASE WHEN n % 10 = 0 134 THEN 1 + ABS(CHECKSUM(NEWID())) % 30 135 ELSE 0 136 END 137 AS orderdate 138 FROM dbo.Nums 139 WHERE n <= @numorders 140 ORDER BY CHECKSUM(NEWID()); 141 142 CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate); 143 144 CREATE NONCLUSTERED INDEX idx_nc_sid_od_cid 145 ON dbo.Orders(shipperid, orderdate, custid); 146 147 CREATE UNIQUE INDEX idx_unc_od_oid_i_cid_eid 148 ON dbo.Orders(orderdate, orderid) 149 INCLUDE(custid, empid); 150 151 CREATE NONCLUSTERED INDEX idx_nc_a0101_a0104_a0107 152 ON dbo.a01(a0101,a0104,a0107) 153 ALTER TABLE dbo.Orders ADD 154 CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(orderid), 155 CONSTRAINT FK_Orders_Customers 156 FOREIGN KEY(custid) REFERENCES dbo.Customers(custid), 157 CONSTRAINT FK_Orders_Employees 158 FOREIGN KEY(empid) REFERENCES dbo.Employees(empid), 159 CONSTRAINT FK_Orders_Shippers 160 FOREIGN KEY(shipperid) REFERENCES dbo.Shippers(shipperid); 161
