SQL Server Custom Paging

You can use a query of the following form to retrieve records by row number from Microsoft SQL Server:

SELECT *
FROM (SELECT TOP {0} *
FROM (SELECT TOP {1} *
FROM {2}
ORDER BY {3}) AS t1
ORDER BY {3} DESC) AS t2
ORDER BY {3}

Replace:
{0} with the page size (the number of records displayed on each page),
{1} with the page size * page number (1-based),
{2} with the name of the table you wish to query, and
{3} with a field name.

The following example retrieves rows 41-50 from the "Products" table of the Northwind database:

SELECT *
FROM (SELECT TOP 10 *
FROM (SELECT TOP 50 *
FROM Products
ORDER BY ProductID) AS t1
ORDER BY ProductID DESC) AS t2
ORDER BY ProductID

You can combine this query technique with custom paging to make DataGrid paging more efficient. With default paging, you must initialize the data source with all records displayed on all pages. With custom paging, you can initialize the data source with just those records that pertain to the current page.