/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

"Indexed views"
2011-03-24
*****************************************************/

set nocount on
go

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

create table dbo.Clients
(
	ClientId int not null,
	ClientName varchar(32),
	
	constraint PK_Clients
	primary key clustered(ClientId)
)
go

create table dbo.Orders
(
	OrderId int not null identity(1,1),
	Clientid int not null,
	OrderDate datetime not null,
	OrderNumber varchar(32) not null,
	Amount smallmoney not null,
	
	Placeholder char(100) not null
		constraint Def_Orders_Placeholder
		default 'a',
			
	constraint PK_Orders
	primary key clustered(OrderId)
)
go


;with CTE(Num)
as
(
	select 0

	union all

	select Num + 1
	from CTE 
	where Num < 100
)
insert into dbo.Clients(Clientid, ClientName)
	select Num, 'Client ' + convert(varchar(32),Num)
	from CTE
option (MAXRECURSION 0)
go

;with CTE(Num)
as
(
	select 0

	union all

	select Num + 1
	from CTE 
	where Num < 100000
)
insert into dbo.Orders(Clientid, OrderDate, OrderNumber, Amount)
	select 
		Num % 100, 
		DATEADD(day,-Num % 365, GetDate()), 
		'Order: ' + convert(varchar(32),Num), 
		Num % 100
	from CTE
option (MAXRECURSION 0)
go


set statistics io on
go

-- no views
select 
	c.ClientId, c.ClientName,
	count(o.OrderId) as [NumOfOrders],
	sum(o.Amount) as [TotalAmount]
from
	dbo.Clients c join dbo.Orders o on
		c.ClientId = o.ClientId 
group by
	c.ClientId, c.ClientName
having 
	sum(o.Amount) > 90000
go

--creating indexing view
create view dbo.vClientWithOrders(
	ClientId, ClientName, 
	NumOfOrders, TotalAmount
)
with schemabinding
as
	select 
		c.ClientId, c.ClientName,
		count_big(*) as NumOfOrders,
		sum(o.Amount) as TotalAmount
	from
		dbo.Clients c join dbo.Orders o on
			c.ClientId = o.ClientId 
	group by
		c.ClientId, c.ClientName
go

create unique clustered index 
	IDX_vClientWithOrders_ClientId
on dbo.vClientWithOrders(ClientId)
go

--now run select
select * 
from dbo.vClientWithOrders 
where TotalAmount > 90000
go

-- now let's run original select again 
-- Enterprise edition?
select 
	c.ClientId, c.ClientName,
	count(o.OrderId) as [NumOfOrders],
	sum(o.Amount) as [TotalAmount]
from
	dbo.Clients c join dbo.Orders o on
		c.ClientId = o.ClientId 
group by
	c.ClientId, c.ClientName
having 
	sum(o.Amount) > 90000
go


-- standard edition
select @@version

select * 
from dbo.vClientWithOrders 
where TotalAmount > 90000
go

select * 
from dbo.vClientWithOrders with (noexpand)
where TotalAmount > 90000
go

select 
	c.ClientId, c.ClientName,
	count(o.OrderId) as [NumOfOrders],
	sum(o.Amount) as [TotalAmount]
from
	dbo.Clients c join dbo.Orders o on
		c.ClientId = o.ClientId 
group by
	c.ClientId, c.ClientName
having 
	sum(o.Amount) > 90000
go


-- performance implications
insert into dbo.Orders(Clientid,OrderDate,
	OrderNumber,Amount)
values(1,getDate(),'123',1000)
go

drop view dbo.vClientWithOrders
go

insert into dbo.Orders(Clientid,OrderDate,
	OrderNumber,Amount)
values(1,getDate(),'123',1000)
go
