Paging results in MS SQL Server

One of the thngs I like about my job is solving problems. In particular problems where there is a bit of thought involved. The particular problem I had is that for a search interface I’m building a requirement is the ability to page result set. This is easy using MySQL as it has a LIMIT keyword that limits the results to a certain number, and allows an offset, so for example you to retrieve results 16-20 you would use LIMIT 15,5.

Unfortunately, SQL Server does not have an equivalent to the offset argument. Its nearest is TOP n, which returns the first n rows.

This puzzled me for a few days, but then the answer came to me in a flash of inspiration: Results 20-16 would be the first 5 rows of a TOP 5 statement, with a nested query with a TOP 20 statement in the opposite order. Then I could just reverse the order to get the result the right way around.

So here’s what I came up with:

SELECT * FROM (
	SELECT TOP x * FROM (
		SELECT TOP y fields
		FROM table
		WHERE conditions
		ORDER BY table.field  ASC) as foo
	ORDER by field DESC) as bar
ORDER by field ASC

x is the number of rows you want returned and y is x+offset.

After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. For the queries I was running it was only about 2%.

Source: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/