Plan Regression with Sequence Projections by Paul White NZ

In SQL Server 2008, the optimizer is able to push suitable predicates below a sequence project. In 2008 R2, pushing selections is broken for sequence projections that require a segment apply, namely NTILE and windowed aggregates. (This regression does not appear, or is fixed, in SQL Server 2012).

(tested on 2008 build 5768 and R2 build 2806)

USE tempdb;
GO
CREATE TABLE dbo.T (x int PRIMARY KEY, y int)
GO
-- Predicate successfully pushed in both 2008 and R2
SELECT z.* FROM
(
    SELECT
        t.x,
        t.y,
        rn = ROW_NUMBER() OVER(
            PARTITION BY t.x
            ORDER BY t.y)
    FROM dbo.T AS t
) AS z
WHERE z.x = 1;
GO
-- Predicate pushed to a seek on 2008
-- Not pushed on 2008 R2
SELECT z.* FROM
(
    SELECT
        t.x,
        t.y,
        rn = MAX(t.y) OVER(
            PARTITION BY t.x)
    FROM dbo.T AS t
) AS z
WHERE z.x = 1;
GO
-- Pushed to a seek on 2008
-- No seek and COUNT(*) computed *twice* on 2008 R2
SELECT z.* FROM
(
    SELECT
        t.x,
        t.y,
        rn = NTILE(4) OVER(
            PARTITION BY t.x
            ORDER BY t.y)
    FROM dbo.T AS t
) AS z
WHERE z.x = 1;
GO