SQL and the like

BUG - 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, obviously there is potential for filtering on the ranking function, but when doing exactly the same on 2008 I 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. I've raised a connect bug report here if you feel that it is something that Microsoft should invest some time in fixing.