Server-side Paging with SQL Server

ROSCA.NET - Server-side Paging with SQL Server: "Server-side Paging with SQL Server
by Andrew Rosca
Paging records is a common, necessary practice for presenting large amounts of information to a user. In addition to making the data easier to understand and browse, paging also plays an important role in improving application performance. Retrieving and displaying large amounts of information constitutes an overhead that is often unnecessary"

SELECT TOP page_size * FROM table WHERE primary_key NOT IN (SELECT TOP page_size * (page_number - 1) primary_key FROM table WHERE filter_conditions ORDER BY sort_field) AND filter_criteria ORDER BY sort_field


SELECT * FROM table WHERE primary key IN (SELECT TOP page_size primary_key FROM table WHERE primary_key NOT IN (SELECT TOP page_size * (page_number - 1) primary_key FROM table WHERE filter_conditions ORDER BY sort_field) AND filter_criteria ORDER BY sort_field) ORDER BY sort_field

EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'


'Northwind.dbo.Orders A JOIN Northwind.dbo.Customers B ON A.CustomerID = B.CustomerID' '(SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > ''8/1/1996'') AS tbl'