Presentation Code
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
go
select o.orderid, d.qty, c.contactname, p.productname
from sales.Orders as o
inner join sales.Customers as c on o.custid=c.custid
inner join sales.OrderDetails as d on o.orderid = d.orderid
inner join Production.Products as p on p.productid = d.productid
where c.contactname = 'Benito, Almudena'
and p.productname = 'Product VJZZH';
go
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
go
with a as
(
select o.orderid, c.contactname
from sales.Orders as o
inner join sales.Customers as c on o.custid=c.custid
), b as
(
select d.qty, p.productname ,d.orderid
from sales.OrderDetails as d
inner join Production.Products as p on p.productid = d.productid
)
select a.orderid, b.qty, a.contactname, b.productname
from a
inner join b on a.orderid = b.orderid
where a.contactname = 'Benito, Almudena'
and b.productname = 'Product VJZZH';
go
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
go
with a as
(
select o.orderid, c.contactname
from sales.Orders as o
inner join sales.Customers as c on o.custid=c.custid
), b as
(
select d.qty, p.productname ,d.orderid
from sales.OrderDetails as d
inner join Production.Products as p on p.productid = d.productid
)
select a.orderid, b.qty, a.contactname, b.productname
from a
inner join b on a.orderid = b.orderid
where a.contactname = 'Benito, Almudena'
and b.productname = 'Product VJZZH'
option (FORCE ORDER)
go
CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
go
select o.orderid, d.qty, c.contactname, p.productname
from
(
sales.Orders as o
inner join sales.Customers as c on o.custid=c.custid
)
inner join
(
sales.OrderDetails as d
inner join Production.Products as p on p.productid = d.productid
) on o.orderid = d.orderid
where c.contactname = 'Benito, Almudena'
and p.productname = 'Product VJZZH'
option (FORCE ORDER);
go