CREATE TABLE customer (
	id int PRIMARY KEY,
	descr varchar( 50 )
)

DECLARE @i int
SET @i =  0 
WHILE @i <  100000  BEGIN
	INSERT INTO customer SELECT @i , 'customer' + STR(@i)
	SET @i = @i +  1 
END

CREATE TABLE [order] (
	id int identity( 1000000 ,  1 ) PRIMARY KEY,
	customer_id int REFERENCES customer (id),
	descr varchar( 50 ),
	cost decimal ( 9 , 2 ),
	[date] datetime,
	flag char( 1 ),
)

SET @i =  0 
WHILE @i <  10  BEGIN
	INSERT INTO [Order] (customer_id, descr, cost, [date], flag)
	SELECT id, 'Order_' + str(id) + '_' + str(@i), id, GETDATE() - (id/ 100 ), (CASE id% 2  WHEN  0  THEN 'Y' ELSE 'N' END)
	FROM [Customer]
	SET @i = @i +  1 
END

select c.id
, c.descr
, sum(cost)
from customer c, [order] o
where c.id = o.customer_id
and flag = 'Y'
and [date] between '20040101' and '20041231'
group by c.id, c.descr
-- Table 'customer'. Scan count 1, logical reads 434, physical reads 0, read-ahead reads 0.
-- Table 'order'. Scan count 1, logical reads 7718, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 1640 ms,  elapsed time = 1768 ms.

CREATE TABLE data_s
(id_p int,
id_s int,
data varchar( 50 ),
PRIMARY KEY(id_p, id_s))

INSERT INTO data_s(id_p, id_s, Data)
SELECT  5 , id, descr
FROM [Order]
INSERT INTO data_s(id_p, id_s, Data)
SELECT  2 , id, descr
FROM [customer]

CREATE TABLE data_i
(id_p int,
id_s int,
data int,
PRIMARY KEY(id_p, id_s))

INSERT INTO data_i(id_p, id_s, Data)
SELECT  3 , id, id
FROM [Order]
INSERT INTO data_i(id_p, id_s, Data)
SELECT  1 , id, id
FROM [customer]
INSERT INTO data_i(id_p, id_s, Data)
SELECT  4 , id, customer_id
FROM [Order]

CREATE TABLE data_dc
(id_p int,
id_s int,
data decimal( 12 , 2 ),
PRIMARY KEY(id_p, id_s))

INSERT INTO data_dc(id_p, id_s, Data)
SELECT  6 , id, cost
FROM [Order]

CREATE TABLE data_dt
(id_p int,
id_s int,
data datetime,
PRIMARY KEY(id_p, id_s))

INSERT INTO data_dt(id_p, id_s, Data)
SELECT  7 , id, [Date]
FROM [Order]

CREATE TABLE data_c
(id_p int,
id_s int,
data char( 1 ),
PRIMARY KEY(id_p, id_s))

INSERT INTO data_c(id_p, id_s, Data)
SELECT  8 , id, flag
FROM [Order]
GO

create view vcustomers as
select a.data as customer_id, b.data as customer_desc
from data_i a, data_s b
where a.id_s = b.id_s
and a.id_p =  1 
and b.id_p =  2 
GO

create view vorders as
select a.data as order_id, b.data as customer_id, d.data as order_cost,
e.data as order_date, f.data as order_flag
from data_i a, data_i b, data_dc d, data_dt e, data_c f
where
a.id_s = b.id_s
and a.id_s = d.id_s
and a.id_s = e.id_s
and a.id_s = f.id_s
and a.id_p =  3 
and b.id_p =  4 
and d.id_p =  6 
and e.id_p =  7 
and f.id_p =  8 
GO

select vcustomers.customer_id, customer_desc , sum(order_cost) from vcustomers, vorders
where vcustomers.customer_id = vorders.customer_id
and order_flag = 'Y'
and order_date between '20040101' and '20041231'
group by vcustomers.customer_id, customer_desc

-- Table 'data_i'. Scan count 3, logical reads 5147, physical reads 0, read-ahead reads 0.
-- Table 'data_dc'. Scan count 1, logical reads 3021, physical reads 0, read-ahead reads 0.
-- Table 'data_c'. Scan count 1, logical reads 2093, physical reads 0, read-ahead reads 0.
-- Table 'data_dt'. Scan count 1, logical reads 3098, physical reads 0, read-ahead reads 0.
-- Table 'data_s'. Scan count 1, logical reads 487, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 6407 ms,  elapsed time = 6538 ms.