I'm not one for automatically poo-pooing new ideas, new practices and new technology.  Rather I like to take a measured view and try to evaluate based upon merits.

One of the new features in Denali is paging which is now supported by the FETCH option of the ORDER BY clause.  On the face of it, it does seem like a really neat addition to the product set,  but as ever we do need to find out what the real costs associated with a query are.

So, using Paul Whites (blog|twitter) rather excellent series on paging as a comparison base ive created a simple stored proc using FETCH.

DROP PROCEDURE dbo.DenaliPageFetch
GO
CREATE PROCEDURE dbo.DenaliPageFetch   @PageSize   BIGINT,
                                       @PageNumber BIGINT

AS

SELECT  P2.post_id,
        P2.thread_id,
        P2.member_id,
        P2.create_dt,
        P2.title,
        P2.body
FROM    dbo.Post P2
ORDER   BY p2.post_id ASC
OFFSET  @PageSize * (@PageNumber-1) ROWS
FETCH NEXT @PageSize ROWS ONLY

and then i’ve compared the performance of Paul’s FetchPageKeySeek procedure to my new DenaliPageFetch.  Starting of at a low range , 1st page of 50 rows, the denali option does indeed give better performance , but by jumping to the 200th page of 50 rows we see a rather different picture emerge.

I think those figures speak for themselves,  so does the execution plan give a reason for this high cost ?

Yup, that’s right is has scanned through the top 10000 rows.  Admittedly this is only CTP1 and maybe Microsoft will invest some more time and effort into this area , but right now im not going to.