Quassnoi,

At the risk of interrupting your flow here, are you aware that you are missing something very important from your 'group-wise min/max (with ties) discussion?

There is a QO transformation, available in both 2005 and 2008, which produces a plan which performs as well as the best methods you show, regardless of cardinality or distribution. No recursive CTEs and no analytic functions required. Excellent performance, without unnecessary complexity.

Please consider the following code, using the sample data from your blog:

WITH RowIDs

AS (

SELECT DD.id

FROM [20091201_ties].t_distinct DD

WHERE DD.lorderer =

(

SELECT MIN(DI.lorderer)

FROM [20091201_ties].t_distinct DI

WHERE DI.glow = DD.glow

)

)

SELECT COUNT(*),

SUM(LEN(LookUp.stuffing))

FROM RowIDs

CROSS

APPLY (

SELECT stuffing

FROM [20091201_ties].t_distinct TD

WHERE TD.id = RowIDs.id

) LookUp;

That code uses the 'lorderer' column. Just change 'lorderer' to 'orderer' in the CTE for the second run.

Here are the actual execution plans for both:

One other thing. I notice that the index definitions given in your blog effectively INCLUDE the id column since that is the clustering key for the table. The id therefore only exists at the leaf level of the index, which makes it unavailable at higher levels. (As an aside, the name of the index seems to imply that id is a key column, rather than an include.)

For the amount of extra space required to store the id at non-leaf levels, you might consider adding the id column explicitly to the index. Not only can you then mark the index as UNIQUE (which may help the optimizer), you allow the QO more options when considering transformations.

The 'segment top' method I demonstrate above requires all referenced columns (in the CTE section) to be explicit keys in the index (and textually referenced in key order). So, if you wanted to change the query do a MIN or MAX ordered on the *id* column at any stage, it would need to be part of the key, not just an include.

Cheers

Paul

(edit to fix graphical plans)