Why does putting a WHERE clause outside view have terrible performance

Question

Let's say you have a view:

CREATE VIEW dbo.v_SomeJoinedTables AS
SELECT
    a.date,
    a.Col1,
    b.Col2,
    DENSE_RANK() 
      OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something
FROM a JOIN b on a.date = b.date

I've found that the performance of:

SELECT *
FROM v_SomeJoinedTables
WHERE date > '2011-01-01'

is much worse than

SELECT *, 
   DENSE_RANK() 
     OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something
FROM a JOIN b ON a.date = b.date
WHERE a.date > '2011-01-01'

I'm very suprised that the query plan for these two statements are not the same.

I've also tried using an inline table valued function, but the query still takes 100-1000 times longer than the code where I copy and paste the view logic.

Any ideas?

Answer 1

It's called "Predicate pushing" aka deferred filtering.

SQL Server doesn't always realise the WHERE can be applied "earlier", inside the view effectively.

It has been mitigated in SQL Server 2008 to work more as expected