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

Manbeen[MSFT] on 7/22/2013 at 9:32 AMThank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we have decided that we will not fix this bug in SQL Server 2008, since the issue does not occur in SQL Server 2012 or the latest version. Thanks again for reporting the product issue and continued support in improving our product.

Sincerely,

Manbeen