Efficient paging using SQL script

jeantoledo

4.75/5 (4 votes)

10 Jan 2012CPOL

12.2K

Take a look at this:DECLARE @ActualRow = 1; /*Start in first item*/DECLARE @ItensCount = 100; /*100 itens for page*/DECLARE @Page = 1; /*Start in first page*/SELECT * FROM(SELECT *, ROWNUMBER() OVER(ORDER BY Name) as ROWNUM FROM Users) as UsWHERE ROWNUM BETWEEN...

Take a look at this:

SQL

DECLARE @ActualRow = 1;      /*Start in first item*/
DECLARE @ItensCount = 100;   /*100 itens for page*/
DECLARE @Page = 1;           /*Start in first page*/

SELECT * FROM
(SELECT *, ROWNUMBER() OVER(ORDER BY Name) as ROWNUM FROM Users) as Us
WHERE ROWNUM BETWEEN @ActualRow AND (@ActualRow + @ItensCount / @page) -1

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)