2008 sql 揭秘 第4章的数据库脚本

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 ; --教据分布设置 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 dbo.Customers( custid CHAR(11) NOT NULL, custname NVARCHAR(50) NOT NULL ); INSERT INTO dbo.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 dbo.Nums where n<=@numcusts; ALTER TABLE dbo.Customers ADD CONSTRAINT Pk_Customers primary key(custid); --创建和镇充Employees来 CREATE TABLE dbo.Employees (empid INT NOT NULL primary key, firstname NVARCHAR(25) not NULL, lastname NVARCHAR(25) NOT NULL); INSERT INTO dbo.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 dbo.Nums where n<=@numemps; create table dbo.Shippers( shipperid varchar(5) not null, shippername nvarchar(50) not null ); insert into dbo.Shippers(shipperid,shippername) select shipperid,N‘Shipper_‘+shipperid as shippername from (select char(ascii(‘A‘)-2+2*n) as shipperid from dbo.Nums where n<=@numshippers) as d; alter table dbo.Shippers add constraint PK_Shippers primary key(shipperid); create table dbo.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 dbo.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) -- late arrival with earlier date -case when n%10=0 then 1+abs(CHECKSUM(NewId()))%30 else 0 end as orderdate from dbo.Nums where n<=@numorders order by CHECKSUM(NEWID()); create clustered index idx_cl_od on dbo.orders(orderdate); create nonclustered index idx_nc_sid_od_i_cid on dbo.Orders(shipperid,orderdate) include(custid); create unique index idx_unc_od_oid_i_cid_eid on dbo.Orders(orderdate,orderid) include(custid,empid); alter table dbo.Orders add constraint PK_Orders primary key nonclustered(orderid), constraint FK_Orders_Customers foreign Key(custid) references dbo.Customers(custid), constraint FK_Orders_Employess foreign Key(empid) references dbo.Employees(empid), constraint FK_Orders_Shippers foreign Key(shipperid) references dbo.Shippers(shipperid); go

 

2008 sql 揭秘 第4章的数据库脚本

标签：new   start   tom   employees   存在   脚本   table   创建   master