Use of ranking functions result in an inefficient query plan

In SQL2005 (9.00.4207.00), if you use a ranking function within a view or CTE, then an inefficient query plan can be produced.

First off in Adventure works create this index

Create Index idxLastName on Person.Contact(LastName)

and then consider these similar queries:

select ContactId,
       LastName,
     row_number() over(partition by LastName order by ContactId) 
from [AdventureWorks].[Person].[Contact] 
where LastName = 'Smith'
go
create view vwContacts
as
select ContactId,
       LastName,
       rown = row_number() over(partition by LastName order by ContactId) 
from [AdventureWorks].[Person].[Contact] 
go
Select * from vwContacts  
 where LastName = 'Smith'
 go 

You should now see two vastly different query plans

Notice in “Query 2” that Filter ?

Wow.. So it did calculate the entire query and then filter on the results.  At first i thought that i was being unfair but when doing exactly the same on 2008 in get index seeks on both sides

CTE’s also suffer from the same problem, although they are easier to rewrite than a view to utilize a seek.

with cteLastNameRow(ContactId,LastName,Row_number)
as(
    select ContactId,
           LastName,
           row_number() over(partition by LastName order by ContactId) 
    from [AdventureWorks].[Person].[Contact] 
    )
select * 
  from cteLastNameRow
 where LastName = 'Smith'
go

Another index scan.

So , be careful when using ranking functions and as ever,  investigate the query execution plan on EVERY query.