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...