SQL Server 2008 Paging Methods

How to do pagination in SQL Server 2008

You can try something like

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

DECLARE @PageSize INT,
        @Page INT

SELECT  @PageSize = 10,
        @Page = 2

;WITH PageNumbers AS(
        SELECT Val,
                ROW_NUMBER() OVER(ORDER BY Val) ID
        FROM    @Table
)
SELECT  *
FROM    PageNumbers
WHERE   ID  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)

Efficient pagination in SQL Server 2008 R2

depending on the [obscenely long select query] structure you may be able to use a temp table or table variable:

- fill a temp table with the ids of the matching rows

- count the temp table rows to calculate the number of pages

- to retrieve the results for the caller build a query with the temp table joined with the relevant database tables

What is the best way to paginate results in SQL Server

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

Pagination query for mssql server 2008 Throwing Incorrect syntax near 'OFFSET'

Here is my work around and working fine now.

SELECT * FROM   (SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownumber,*
        FROM document)  as somex  WHERE  rownumber >= (1+1)*10-9
                         AND rownumber <=(1+1)*10

In the above query i am replacing (1+1) with (pageNUmber+1).

Please feel free to suggest me if any elegant way available.

MSSQL 2008 SP pagination and count number of total records

After more research I fond another way of doing this:

with Paging(RowNo, ID, Name, TotalOccurrences) AS
(
    ROW_NUMBER() over (order by TotalOccurrences desc) as RowNo, V.ID, V.Name,    R.TotalOccurrences FROM dbo.Videos V INNER JOIN .... 
)
select  RowNo, ID, Name, TotalOccurrences, (select COUNT(*) from Paging) as TotalResults  from Paging where RowNo between (@PageNumber - 1 )* @PageSize + 1 and @PageNumber * @PageSize;

I think that this has better performance than calling two times the query.

SQL Server 2008 Skip Take with long parameter

Well, if it is necessary at all - expected the case in comments where you have to skip billions to take only 20 - to read as lot as possible data.

• You could use a stored procedure that will get the amount to skip and
  take.
• Another approach would be to execute a plain SQL on your dbContext's database property.

If you want to use LINQ and you have increment IDs you could give following a try - but it is a bit more expensive and it will not cover the long value for data amount to retrieve:

At first execution you have to determine the ID of int.MaxValue-1. After that point you could re-use the logic for rest of paging. (maybe depending on a flag)
Use the ID as where clause (lower border) and add a Take with needed amount. From result's last record you will save the ID for next paging call where you will use it instead of first determined ID.

A possible approach code (not tested) could be (will not cover all your cases without any modifications):

private long _lastId;

public IEnumerable<Students> GetPage(int toTake)
{
    List<Students> result;

    result = isFirstPage
        ? _context.Students
            .Take(toTake)
            .ToList();
        : _context.Students
            .Where(s => s.Id > _lastId)
            .Take(toTake)
            .ToList();

    _lastId = result.LastOrDefault()?.Id ?? 0;


    return result;
}

---

Related Topics