T_Sql查询的测试数据sql

--创建数据库
SET NOCOUNT ON;
USE master
if DB_ID('Performance') is null
    Create DATABASE Performance
GO
USE Performance;
GO

--
SET NOCOUNT ON;
IF OBJECT_ID('dbo.Nums','U') IS NOT NULL
    DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT,@rc AS INT;
SET @max=1000000;
SET @rc=1;
INSERT INTO dbo.Nums(n) VALUES(1);
WHILE @rc*2<=@max
BEGIN
    INSERT INTO dbo.Nums(n) SELECT n+@rc FROM dbo.Nums;
    SET @rc=@rc*2;
END
INSERT INTO dbo.Nums(n)
    SELECT n+@rc FROM dbo.Nums WHERE n+@rc<=@max;
GO
--
IF OBJECT_ID('dbo.EmpOrders','V') IS NOT NULL
    DROP VIEW dbo.EmpOrders;
GO
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
    DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers','U') IS NOT NULL
    DROP TABLE dbo.Customers;
GO
IF OBJECT_ID('dbo.Employees','U') IS NOT NULL
    DROP TABLE dbo.Employees;
GO
IF OBJECT_ID('dbo.Shippers','U') IS NOT NULL
    DROP TABLE dbo.Shippers;
GO

--数据分布设置
DECLARE
    @numorders AS INT,
    @numcusts AS INT,
    @numemps AS INT,
    @numshippers AS INT,
    @numyears AS INT,
    @startdate AS DATETIME;

SELECT
    @numorders = 1000000,
    @numcusts = 20000,
    @numemps = 500,
    @numshippers = 5,
    @numyears = 4,
    @startdate = '20050101';

--创建和填充Customers表
CREATE TABLE Customers
(
    custid char(11) NOT NULL,
    custname NVARCHAR(50) NOT NULL
)
INSERT INTO Customers(custid,custname)
    SELECT 
    'C' + RIGHT('000000000'+CAST(n AS VARCHAR(10)),10) AS custid,
    N'Cust_' + CAST(n AS VARCHAR(10)) AS custname
    FROM Nums
    WHERE n<@numcusts;
ALTER TABLE Customers ADD
    CONSTRAINT PK_Customers PRIMARY KEY(custid);

--创建和填充Employees表
CREATE TABLE Employees
(
    empid INT NOT NULL,
    firstname NVARCHAR(25) NOT NULL,
    lastname NVARCHAR(25)  NOT NULL
);

INSERT INTO Employees(empid,firstname,lastname)
    SELECT n AS empid,
    N'Fname_'+CAST(n AS NVARCHAR(10)) AS firstname,
    N'Lname_'+CAST(n AS NVARCHAR(10)) AS lastname
    FROM Nums
    WHERE n<=@numemps;

ALTER TABLE Employees ADD
    CONSTRAINT PK_Employees PRIMARY KEY(empid);

--创建和填充Shippers
CREATE TABLE Shippers
(
    shipperid VARCHAR(5) NOT NULL,
    shippername NVARCHAR(50) NOT NULL,
)
INSERT INTO Shippers(shipperid,shippername)
    SELECT shipperid,N'Shipper_' + shipperid AS shippername
    FROM (SELECT CHAR(ASCII('A')-2+2*n) AS shipperid
        FROM Nums
    WHERE n<=@numshippers) AS D;
ALTER TABLE Shippers ADD
    CONSTRAINT PK_Shippers PRIMARY KEY(shipperid);

--创建和填充Orders
CREATE TABLE Orders
(
    orderid INT NOT NULL,
    custid CHAR(11) NOT NULL,
    empid INT NOT NULL,
    shipperid VARCHAR(5) NOT NULL,
    orderdate DATETIME NOT NULL,
    filler CHAR(155) NOT NULL DEFAULT('a')
);
INSERT INTO Orders(orderid,custid,empid,shipperid,orderdate)
    SELECT n AS orderid,
        'C'+RIGHT('000000000'
            +CAST(
                1+ABS(CHECKSUM(NEWID())) % @numcusts
                AS VARCHAR(10)),10) AS custid,
        1+ABS(CHECKSUM(NEWID())) % @numemps AS empid,
        CHAR(ASCII('A')-2
            +2*(1+ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid,
            DATEADD(DAY,n/(@numorders/(@numyears*365.25)),@startdate)
            -CASE WHEN n%10=0
                THEN 1+ ABS(CHECKSUM(NEWID()))%30
                ELSE 0
                END AS orderdate
    FROM Nums
    WHERE n<=@numorders
    ORDER BY CHECKSUM(NEWID());

CREATE CLUSTERED INDEX idx_c1_od ON Orders(orderdate);
CREATE NONCLUSTERED INDEX idx_nc_sid_od_i_cid
    ON Orders(shipperid,orderdate)
    INCLUDE(custid);
CREATE UNIQUE INDEX idx_unc_od_oid_i_cid_eid
    ON Orders(orderdate,orderid)
    INCLUDE(custid,empid);

ALTER TABLE Orders ADD
    CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(orderid),
    --CONSTRAINT FK_Orders_Customers
    --FOREIGN KEY (custid) REFERENCES Customers(custid),
    CONSTRAINT FK_Orders_Employees
    FOREIGN KEY (empid) REFERENCES Employees(empid),
    CONSTRAINT FK_Orders_Shippers
    FOREIGN KEY (shipperid) REFERENCES Shippers(shipperid);
GO