Just to point out a possible improvement on the first query in part 3.

The sql engine has to match all rows from the subquery to the outer query. This is because the filtering is outside the subquery.

SELECT CustomerID FROM Sales.SalesOrderHeader OH

WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail

WHERE SalesOrderID = OH.SalesOrderID) > 70;

Just by adding a GROUP BY clause, the work is cut by a 1/4, here are two suggestions, the latter slightly faster :cool:;

SELECT CustomerID FROM Sales.SalesOrderHeader OH

WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail

WHERE SalesOrderID = OH.SalesOrderID

GROUP BY SalesOrderID) > 70;

And

SELECT

SOH.CustomerID

FROM

(

SELECT

COUNT(*) AS SOD_COUNT

,SOD.SalesOrderID

FROM Sales.SalesOrderDetail SOD

GROUP BY SOD.SalesOrderID

) AS X

INNER JOIN Sales.SalesOrderHeader SOH

ON X.SalesOrderID = SOH.SalesOrderID

WHERE X.SOD_COUNT > 70;