创建一个sql临时表
use master;
set nocount on;
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 = 5000000;
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 =5000000,
@numCusts =10000,
@numemps =1000,
@numshippers = 10,
@numyears =5,
@startdate = '20050101';
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);
create table dbo.employees
(
empid int not null,
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;
alter table dbo.employees add constraint PK_Employees primary key (empid);
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)
-- line
-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());
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_Employees foreign key(empid) references dbo.employees(empid),
constraint FK_Orders_Shippers foreign key(shipperid) references dbo.shippers(shipperid);
go
/* */
if OBJECT_ID('dbo.nums', 'u') is not null
drop table dbo.nums;
go
