A scalar subquery is a subquery that returns a single row. Some scalar subqueries are obvious. For instance: create table T1 ( a int , b int ) create table T2 ( a int , b int ) select * from T1 where T1 . a > ( select max ( T2 . a...

In my prior two posts, I wrote about the stream aggregate operator. Stream aggregate is great for scalar aggregates and for aggregations where we have an index to provide a sort order on the group by column(s) or where we need to sort anyhow (e.g., due...

There are two physical operators that SQL Server uses to compute general purpose aggregates where we have a GROUP BY clause. One of these operators is stream aggregate which as we saw last week is used for scalar aggregates . The other operator is hash...

Aggregation refers to the collapse of a larger set of rows into a smaller set of rows. Typical aggregate functions are COUNT, MIN, MAX, SUM, and AVG. SQL Server also supports other aggregates such as STDEV and VAR. I’m going to break this topic down...