In some of my past posts, I've discussed how SQL Server implements aggregation including the stream aggregate and hash aggregate operators. I also used hash aggregation as an initial example in my introductory post on parallel query execution. In this post, I'll look at a partial aggregation. Partial aggregation is a technique that SQL Server uses to optimize parallel aggregation. Before I begin, I just want to note that I also discuss partial aggregation in Inside Microsoft SQL Server 2005: Query Tuning and Optimization. (See the bottom of page 187.)
Let's begin with a simple scalar aggregation example. Recall that a scalar aggregate is an aggregate without a GROUP BY clause. A scalar aggregate always produces a single output row.
CREATE TABLE T (A INT, B INT IDENTITY, C INT,
Recursive CTEs always follow the same pattern. The body of the CTE is a UNION ALL query that combines one or more anchor sub-selects which seed the result set and one or more recursive sub-selects which generate the remainder of the result set. The recursive sub-selects reference the recursive CTE itself.
Let's look at a simple example which computes a list of all employees and their level within the organization. Computing the level with the organization can only be done using a recursive CTE as it requires counting the number of joins required to reach each employee:
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL&n