0

one way is we can use row_number function as follows to pull data from db.

select * from (
    select ROW_NUMBER() over (order by SalesOrderDate desc) as rn, *
    from sales.salesorderheader
) as x
where rn BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index * @PageSize

another way is

;WITH x AS 
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...;

tell me which one is good and when to use which one? thanks

Mou
  • 15,673
  • 43
  • 156
  • 275
  • Second approach at any case, though the queries look different – Pரதீப் Nov 20 '16 at 16:17
  • 2
    Possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) – alroc Nov 20 '16 at 16:18
  • What version of SQL Server? Additional help/support for pagination was added in 2012. – alroc Nov 20 '16 at 16:19
  • It depends what indexes you have and what page number you are requesting. – Martin Smith Nov 20 '16 at 16:28
  • In the possible duplicate link, I prefer [this method](http://stackoverflow.com/a/19609938/243373). – TT. Nov 20 '16 at 16:56
  • Best in what way? Also, wouldn't it be easier for you just to test it, instead of asking from random persons in the internet? – James Z Nov 20 '16 at 17:06

0 Answers0