2008 sql 揭秘 第4章的数据库脚本
时间:2021-07-01 10:21:17
帮助过: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