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

"Views hell"
2011-02-24
*****************************************************/

/* DO NOT RUN IT AGAINST TEMPDB - TEMPDB DOES NOT HAVE JOINS ELIMINATIONS */

set nocount 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

create view dbo.vOrdersWithClients(OrderId, Clientid
	,OrderDate, OrderNumber, Amount, ClientName)
as
	select o.OrderId, o.ClientId, o.OrderDate,
		o.OrderNumber, o.Amount, c.ClientName
	from
		dbo.Orders o join dbo.Clients c on
			o.Clientid = c.ClientId
go

-- enable display execution plan

-- join as expected
select * from dbo.vOrdersWithClients where OrderId = 100
go

-- join - unexpectedly
select OrderId, OrderNumber 
from dbo.vOrdersWithClients where OrderId = 100
go

create view dbo.vOrdersWithClients2(OrderId, Clientid
	,OrderDate, OrderNumber, Amount, ClientName)
as
	select o.OrderId, o.ClientId, o.OrderDate,
		o.OrderNumber, o.Amount, c.ClientName
	from
		dbo.Orders o left outer join dbo.Clients c on
			o.Clientid = c.ClientId
go

select OrderId, OrderNumber 
from dbo.vOrdersWithClients2 where OrderId = 100
go


alter table dbo.Orders with check
add constraint FK_Orders_Clients
foreign key(ClientId)
references dbo.Clients(ClientId)
go

create index IDX_Orders_ClientId 
on dbo.Orders(ClientId)
go

select OrderId, OrderNumber 
from dbo.vOrdersWithClients where OrderId = 100
go



/* Multi column FKs */
create table dbo.Clients2
(
	ClientId int not null,
	Int1 int not null
		constraint DEF_Clients2_Int1
		default(1),
		
	ClientName varchar(32),
	
	constraint PK_Clients2
	primary key clustered(ClientId, Int1)
)
go


create table dbo.Orders2
(
	OrderId int not null identity(1,1),
	Int1 int not null
		constraint DEF_Orders2_Int1
		default(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_Orders2_Placeholder
		default 'a',
			
	constraint PK_Orders2
	primary key clustered(OrderId),
	
	constraint FK_Orders2_Clients2
	foreign key(ClientId, Int1)
	references dbo.Clients2(ClientId,Int1)
)
go


;with CTE(Num)
as
(
	select 0

	union all

	select Num + 1
	from CTE 
	where Num < 100
)
insert into dbo.Clients2(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.Orders2(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

create view dbo.vOrders2WithClients2(OrderId, Clientid
	,OrderDate, OrderNumber, Amount, ClientName)
as
	select o.OrderId, o.ClientId, o.OrderDate,
		o.OrderNumber, o.Amount, c.ClientName
	from
		dbo.Orders2 o join dbo.Clients2 c on
			o.Clientid = c.ClientId and 
			o.Int1 = c.Int1
go


-- Oops

select OrderId, OrderNumber 
from dbo.vOrders2WithClients2 
where OrderId = 100
go

create view dbo.vClientWithOrderCnt(ClientId, ClientName,
	OrderCount)
as
	select c.ClientId, c.ClientName, COUNT(*) as OrderCount
	from 
		dbo.Clients c join dbo.Orders o on
			c.ClientId = o.Clientid
	group by
		c.ClientId, c.ClientName
go

select ClientId 
from dbo.vClientWithOrderCnt 
where ClientId = 1
go
