Please Note: If you have SQL Server 2012 there is a slightly better built-in option available, which I have described here. The method described in this post works for all versions of SQL Server 2005 and later.
This post explains a very efficient way of retrieving pages of data from SQL Server. Rather than retrieving a whole data set and filtering it down with code when generating the display, which could become very slow given a large number of rows, we will only select the 10-20 rows you will be displaying at any one time.
Let's take the following query for example, which returns a list of all the members in the database that have a status of 1.
Let's imagine we want to to view the 2nd page of a 10 record per page listing, in other words we want the rows between 11 and 20. If we used the above query we would have the entire record set returned, then we can use our programming language of choice to loop over the 10 records we want and display them as required. Not very efficient given that this query could potentially return a very large number of records.
Luckily in SQL Server there is a way to reduce this result set to just the records you need for each request.
This query creates a Common Table Expression which selects the data we want while numbering each row using the ROW_NUMBER() function. Then we select the rows we want from the CTE by using the BETWEEN clause. This method is immensely faster than a straight up select query, especially on large data sets, making your pages load significantly faster.
