--create index foo2 on orders(customerid, orderdate) --priority to join
--create index foo1 on orders(orderdate, customerid) --priority to sarg

--create index foo3 on customers(customerid, companyname) --priority to join

/* How to test time!
        declare @starttime    datetime,
                    @endtime        datetime
        select @starttime = getdate()
        --run your query (or queries)
        select @endtime = getdate()
        select 'totaltime' = datediff(ms, @starttime, @endtime)
-- NOTE - if the times are small this could be off by as much as 10 ms
*/
USE northwind
go
set statistics io on
go
--Correlated Subquery
declare @starttime    datetime,
            @endtime        datetime
select @starttime = getdate()
SELECT c.companyname
FROM dbo.customers as c
WHERE EXISTS
	(SELECT  *
    	 FROM dbo.orders as o
	 WHERE c.customerid = o.customerid
               AND o.orderdate >'1/1/95')
select @endtime = getdate()
select 'TotalTime(C)' = datediff(ms, @starttime, @endtime)

-- Join
select @starttime = getdate()
SELECT distinct c.companyname
FROM dbo.customers as c
    JOIN dbo.orders as o 
        ON c.customerid = o.customerid
WHERE o.orderdate >'1/1/95'
select @endtime = getdate()
select 'TotalTime(J)' = datediff(ms, @starttime, @endtime)

-- Nested Subquery
select @starttime = getdate()
SELECT companyname
FROM dbo.customers
WHERE customerid IN
	(SELECT  customerid
    	 FROM dbo.orders
	 WHERE orderdate >'1/1/95')
select @endtime = getdate()
select 'TotalTime(N)' = datediff(ms, @starttime, @endtime)
