Scalar subqueries are an easy way to add aggregations (count, sum, etc.) to queries. But adding lots of these to one statement like this:

select …
( select count ( … ) … ),
( select avg ( … ) … ),
( select max ( … ) … )
from …

Can make your SQL significantly slower. But there's an alternative that can make this much faster:

CROSS APPLY!

Watch this session to learn the ins-and-outs of scalar subqueries, how cross apply can be better, and why scalar subqueries can be the fastest solution in some cases.

Highlights include:

03:50 - What is a scalar subquery
05:50 - Slow running scalar subquery demo
07:10 - Why scalar subqueries are slow
09:30 - Rewriting scalar subqueries as joins
11:35 - Challenges with turning scalar subqueries to joins
14:20 - Cross apply joins
16:20 - Convert scalar subqueries to cross apply
19:00 - Demo of cross apply examples
24:30 - Demo of converting scalar subqueries to cross apply
35:35 - Advantages of using scalar subqueries to scalar subquery caching
39:00 - Demo of optimizer rewrite of scalar subqueries to joins
41:30 - Demo of scalar subquery caching
50:10 - Demo of optimizing function calls in the where clause by using scalar subquery caching
52:40 - Scalar subquery performance scale
54:15 - Recap of SSQ vs joins vs cross apply