What is the fastest way to get the x th block of y rows of an ordered result?

Question

question

William Brewer asked Oct 12, '09

What is the fastest way to get the x th block of y rows of an ordered result?

When a front-end application is using a scrolling listbox, one gets calls to the database for a result consisting of, say, the fourth block of 20 rows of a result bases, perhaps, on date order. If you are supplying a list of customers, one might want to page the list based on the user clicking on the scroll bar. Conventional wisdom has it that one uses the 'Top and Tail' approach, where one gets the result up to the end of the page you want, and then reverse the order before reading just that page you want, both time using TOP to get the numbers you want in the result. Then you end by ordering the page as the user-interface wants...

There must be a quicker way now based on one of the SQL Server 2005 Row-numbering techniques. If so, what is it? (I haven't been asked to do this by the Devs for a while!)

t-sql row_number

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-12T14:21:36Z

Matt Whitfield answered Oct 12, '09

The basic construct is as follows:

SELECT * FROM 
  (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as __RN FROM Table) iDat
WHERE __RN BETWEEN ((@pageNumber - 1) * @rowsPerPage) + 1 and (@pageNumber * @rowsPerPage)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question