I'm slow in applying the new SQL Server functions.

Recently I ran into a request where I needed to pull the newest record for a related table several joins deep.  I finally decided to dig into and use the new rank function to do it.  It resulted in a much better query plan with many fewer reads.

Here is a simple example of using the rank function vs. using a correlated sub-query with the max() function.  In this simple example there is no cost savings either way that I could find, but real world scenarios are never this simple and you may find like I did that it is time to look at the new functions.  Example below:

use tempdb

Create Table ORDERS (ORDERNUMBER int identity(1,1) primary key,REFERENCE int not null,SomeBSColumns varchar(200))

insert into ORDERS (REFERENCE,SomeBSColumns) values (1,'order 1'),(2,'order 2'),(3,'order 3'),(4,'order 4')

Create Table PhoneData (PhoneKey int identity(1,1) primary key, OrderNumber int not null,NotesXML varchar(1000),TimeTag1 datetime)

Create nonclustered index ix_phoneData on PhoneData (orderNumber)

insert into PhoneData (OrderNumber,NotesXML,TimeTag1) values

(1,'Not Received','02/20/2012 16:00:00.000')

,(1,'still not received','02/22/2012 16:00:00.000')

,(1,'loud complaint','02/23/2012 16:00:00.000')

,(1,'finally received','02/24/2012 16:00:00.000')

,(3,'order 3 received, but broken','02/26/2012 16:00:00.000')

,(3,'another replacement sent','02/27/2012 16:00:00.000')

,(4,'finally received','02/22/2012 16:00:00.000')

set statistics io on

SELECT x.* FROM

( SELECT distinct o.ORDERNUMBER,o.REFERENCE, o.SomeBSColumns,

p.NotesXML,p.timetag1,

RANK() over (partition by o.ORDERNUMBER order by p.Timetag1 DESC) as 'RANKING'

FROM ORDERS o

LEFT OUTER JOIN PhoneData p ON o.ORDERNUMBER = p.ORDERNUMBER

) x

WHERE x.RANKING = 1

ORDER BY x.OrderNumber

Select FIQ.*,p.NotesXML

From (

select orders.ORDERNUMBER,orders.REFERENCE, orders.SomeBSColumns,

( select MAX(TimeTag1) as TimeTag1 From PhoneData pIQ -- Alias - PhoneData Inner Query

where (ORDERS.ORDERNUMBER = pIQ.OrderNumber)

)

as TimeTag1

From ORDERS

) FIQ -- Alias = First-Inner-Query

Left Outer Join PhoneData p on

FIQ.ORDERNUMBER = p.OrderNumber

And p.Timetag1 = FIQ.TimeTag1

Added a Whitney CrabApple to the North East Corner.

Whitney Crabapple

Mostly for my own benefit so that I can remember what is planted where, we'll see what makes it.