Pagination in SQL Server

Posted: August 21, 2011 in SQLServer
Tags: ,
Achieve pagination directly in SQL Server using different approaches ...

1. ROW_NUMBER() approach with inner join
SELECT ees.* FROM saleslt.customer ees
JOIN (SELECT customerid, rownum =ROW_NUMBER() over (ORDER BY customerid) FROM saleslt.customer ) eelist
ON eelist.customerid = ees.customerid
WHERE eelist.rownum BETWEEN 21 AND 30

2. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 1
declare @rowsPerPage int, @pageNo int
SET @rowsPerPage = 10
SET @pageNo = 59
begin
WITH SQLPaging
AS
(
SELECT TOP(@rowsPerPage * @pageNo) ResultNum = ROW_NUMBER() OVER (ORDER BY customerid), * FROM saleslt.customer
)
SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNo - 1) * @rowsPerPage)
end

3. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 2
WITH CTE_Customers
AS
(
SELECT CEILING(ROW_NUMBER() OVER (ORDER BY customerid ASC))/10 AS PageNumber, * FROM saleslt.Customer
)
SELECT * FROM CTE_Customers WHERE PageNumber = 10

4. Using sub query
SELECT * FROM
   (SELECT TOP 10 * FROM
     (SELECT TOP 30 * FROM saleslt.customer order by CustomerID asc) AS T2
	ORDER BY customerid DESC) T3
ORDER BY customerid ASC

5/6. Generalizing sub query approach using dynamic query in stroedproc, temporary table approach using dynamic query in storedproc
http://www.beansoftware.com/ASP.NET-Tutorials/Paging-Stored-Procedures.aspx

7. Cursor approach can be seen
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

8. ROWCOUNT approach
DECLARE @Sort int /* the type of the sorting column */
SET ROWCOUNT 21
SELECT @Sort = CustomerID FROM saleslt.customer  ORDER BY CustomerID
SET ROWCOUNT 10
SELECT * FROM saleslt.customer WHERE CustomerID >= @Sort ORDER BY CustomerID
Other References
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s