Efficient paging using SQL script

jeantoledo

4.75/5 (4 votes)

Dec 28, 2011

CPOL

12232

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:

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