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
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -10/25/2007 1:57:00 PM

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,
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -1/18/2008 1:12:00 PM

SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are collectively referred to as ranking functions.  These functions differ from ordinary scalar functions in that the result that they produce for a given row depends on the other rows in the result set.  They also differ from aggregate functions in that they produce exactly one output row for each input row.  Unlike aggregates they do not collapse a set of rows into a single row.  In this post, I'll take a look at ROW_NUMBER - the simplest of all ranking functions.

I'll use the following simple data set for the examples in this post:

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

INSERT T VALUES (0, 1, 8)
INSERT T VALUES (0, 3, 6)
INSERT T VALUES (0, 5, 4)
INSERT T VALUES (0, 7, 2)
INSERT T VALUES (0, 9, 0)
INSERT T VALUES (1, 0, 9)
INSERT T VALUES (1, 2, 7)
INSERT T VALUES (1, 4, 5)
INSERT T VALUES (1, 6, 3)
INSERT T VALUES (1, 8, 1)

Let's begin with the following simple example:

SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T

This query orders the rows in the table by column B and assigns sequential row numbers (much like an identity column) to these rows.  The result looks like so:

PK    A     B     C     RowNumber
----- ----- ----- ----- ----------
6     1     0     9     1
1     0     1     8     2
7     1     2     7     3
2     0     3     6     4
8     1     4     5     5
3     0     5     4     6
9     1     6     3     7
4     0     7     2     8
10    1     8     1     9
5     0     9     0     10

The plan for this query is fairly simple:

  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
  
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -3/19/2008 6:28:00 PM