Implement Paging (Skip/Take) Functionality With This Query

Implement paging (skip / take) functionality with this query

In SQL Server 2012 it is very very easy

SELECT col1, col2, ...
 FROM ...
 WHERE ... 
 ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

If we want to skip ORDER BY we can use

SELECT col1, col2, ...
  ...
 ORDER BY CURRENT_TIMESTAMP
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

(I'd rather mark that as a hack - but it's used, e.g. by NHibernate. To use a wisely picked up column as ORDER BY is preferred way)

to answer the question:

--SQL SERVER 2012
SELECT PostId FROM 
        ( SELECT PostId, MAX (Datemade) as LastDate
            from dbForumEntry 
            group by PostId 
        ) SubQueryAlias
 order by LastDate desc
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

New key words offset and fetch next (just following SQL standards) were introduced.

But I guess, that you are not using SQL Server 2012, right? In previous version it is a bit (little bit) difficult. Here is comparison and examples for all SQL server versions: here

So, this could work in SQL Server 2008:

-- SQL SERVER 2008
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 10,@End = 20;


;WITH PostCTE AS 
 ( SELECT PostId, MAX (Datemade) as LastDate
   ,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
   from dbForumEntry 
   group by PostId 
 )
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY PostId

efficient way to implement paging

Trying to give you a brief answer to your doubt, if you execute the skip(n).take(m) methods on linq (with SQL 2005 / 2008 as database server) your query will be using the Select ROW_NUMBER() Over ... statement, with is somehow direct paging in the SQL engine.

Giving you an example, I have a db table called mtcity and I wrote the following query (work as well with linq to entities):

using (DataClasses1DataContext c = new DataClasses1DataContext())
{
    var query = (from MtCity2 c1 in c.MtCity2s
                select c1).Skip(3).Take(3);
    //Doing something with the query.
}

The resulting query will be:

SELECT [t1].[CodCity], 
    [t1].[CodCountry], 
    [t1].[CodRegion], 
    [t1].[Name],  
    [t1].[Code]
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]) AS [ROW_NUMBER], 
        [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
    FROM [dbo].[MtCity] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

Which is a windowed data access (pretty cool, btw cuz will be returning data since the very begining and will access the table as long as the conditions are met). This will be very similar to:

With CityEntities As 
(
    Select ROW_NUMBER() Over (Order By CodCity) As Row,
        CodCity //here is only accessed by the Index as CodCity is the primary
    From dbo.mtcity
)
Select [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

With the exception that, this second query will be executed faster than the linq result because it will be using exclusively the index to create the data access window; this means, if you need some filtering, the filtering should be (or must be) in the Entity listing (where the row is created) and some indexes should be created as well to keep up the good performance.

Now, whats better?

If you have pretty much solid workflow in your logic, implementing the proper SQL way will be complicated. In that case LINQ will be the solution.

If you can lower that part of the logic directly to SQL (in a stored procedure), it will be even better because you can implement the second query I showed you (using indexes) and allow SQL to generate and store the Execution Plan of the query (improving performance).

Paging with LINQ for objects

You're looking for the Skip and Take extension methods. Skip moves past the first N elements in the result, returning the remainder; Take returns the first N elements in the result, dropping any remaining elements.

See MSDN for more information on how to use these methods: http://msdn.microsoft.com/en-us/library/bb386988.aspx

Assuming you are already taking into account that the pageNumber should start at 0 (decrease per 1 as suggested in the comments) You could do it like this:

int numberOfObjectsPerPage = 10;
var queryResultPage = queryResult
  .Skip(numberOfObjectsPerPage * pageNumber)
  .Take(numberOfObjectsPerPage);

Otherwise if pageNumber is 1-based (as suggested by @Alvin)

int numberOfObjectsPerPage = 10;
var queryResultPage = queryResult
  .Skip(numberOfObjectsPerPage * (pageNumber - 1))
  .Take(numberOfObjectsPerPage);

Is there any better option to apply pagination without applying OFFSET in SQL Server?

You can use Keyset Pagination for this. It's far more efficient than using Rowset Pagination (paging by row number).

In Rowset Pagination, all previous rows must be read, before being able to read the next page. Whereas in Keyset Pagination, the server can jump immediately to the correct place in the index, so no extra rows are read that do not need to be.

For this to perform well, you need to have a unique index on that key, which includes any other columns you need to query.

In this type of pagination, you cannot jump to a specific page number. You jump to a specific key and read from there. So you need to save the unique ID of page you are on and skip to the next. Alternatively, you could calculate or estimate a starting point for each page up-front.

One big benefit, apart from the obvious efficiency gain, is avoiding the "missing row" problem when paginating, caused by rows being removed from previously read pages. This does not happen when paginating by key, because the key does not change.

---

Here is an example:

Let us assume you have a table called TableName with an index on Id, and you want to start at the latest Id value and work backwards.

You begin with:

SELECT TOP (@numRows)
  *
FROM TableName
ORDER BY Id DESC;

Note the use of ORDER BY to ensure the order is correct

In some RDBMSs you need LIMIT instead of TOP

The client will hold the last received Id value (the lowest in this case). On the next request, you jump to that key and carry on:

SELECT TOP (@numRows)
  *
FROM TableName
WHERE Id < @lastId
ORDER BY Id DESC;

Note the use of < not <=

In case you were wondering, in a typical B-Tree+ index, the row with the indicated ID is not read, it's the row after it that's read.

---

The key chosen must be unique, so if you are paging by a non-unique column then you must add a second column to both ORDER BY and WHERE. You would need an index on OtherColumn, Id for example, to support this type of query. Don't forget INCLUDE columns on the index.

SQL Server does not support row/tuple comparators, so you cannot do (OtherColumn, Id) < (@lastOther, @lastId) (this is however supported in PostgreSQL, MySQL, MariaDB and SQLite).

Instead you need the following:

SELECT TOP (@numRows)
  *
FROM TableName
WHERE (
    (OtherColumn = @lastOther AND Id < @lastId)
    OR OtherColumn < @lastOther
)
ORDER BY
  OtherColumn DESC,
  Id DESC;

This is more efficient than it looks, as SQL Server can convert this into a proper < over both values.

The presence of NULLs complicates things further. You may want to query those rows separately.

How do I write LINQ's .Skip(1000).Take(100) in pure SQL?

In SQL Server 2005 and above you can use ROW_NUMBER function. eg.

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 51 AND 60; --BETWEEN is inclusive

---

Related Topics