Is There Any Better Option to Apply Pagination Without Applying Offset in SQL Server

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.

SQL pagination without offset and index column

My solution is only applicable to phoenix 4.7 (hdp 2.5) and the way I found was to sort the data in ascending order with the primary key and if there is the composite key data was sorted as per the first column of the key and then the offset and limit works properly.

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.

Sql best way to pagination

Set based operations perform better. Avoid row by row processing.

We can use row_number assigned by the database and divide by the number of records we want per page to generate a page index. If we truncate/(round and eliminate decimals) we get the desired page index.

Something Like:

SELECT ID
    , SomeName
    , round(ROW_NUMBER() OVER(ORDER BY SomeName ASC)/5,0,1) AS PageIndex
FROM #temp
ORDER BY PageIndex, SomeName

• 5 represents number of records per "page"
• 0 as we don't care about the decimals but we don't want rounding to occur before truncating the decimals.
• 1 to truncate to 0 decimals w/o rounding.

I assume you know you could wrap this in a CTE and add a where clause to get specific pages desired

SQL Server 2012 - Pagination without Order Clause

You can order by column index if that helps:

SELECT First Name + ' ' + Last Name 
FROM Employees 
ORDER BY 1 OFFSET 10 ROWS; 

This will order by the first column. If the ordering column is stored on the UI (controlled by users), then you need to store the column index on the UI and pass that to SQL Server to use in the ordering.

If you simply want to order by the order the records are added to the table, you will need a primary key, identity column. Make sure that is the first column on the table and use ORDER BY 1.

How to use Pagination in stored procedure without using OFFSET MSSQL server?

To solve my Problem i used

ALTER PROCEDURE BooksGetList
    @BookName VARCHAR(50) = null, @BookPublisherId INT = null, @BookCategoryId INT = null, @PageNumber INT = 1, @PageSize INT = 10,@TotalRecords INT = null OUT
    AS BEGIN
    SELECT 
        [Books].[BookId],                           [Books].[BookName],
        [BookCategories].[BookCategoryId],          [BookCategories].[BookCategoryName],
        [BookPublishers].[BookPublisherId],         [BookPublishers].[BookPublisherName],   
        [BookQuantity],                             [Books].[IsActive],
        [Books].[CreatedBy],                        [Books].[CreatedOn],        
        [Books].[ModifiedBy],
        [Books].[ModifiedOn],                       ROW_NUMBER() OVER (ORDER BY BookId) as RowNumber into #TempBooks
    FROM
        [Books] Inner Join BookCategories On [BookCategories].BookCategoryId = [Books].BookCategoryId
                Inner Join BookPublishers On [BookPublishers].BookPublisherId = [Books].BookPublisherId
    Where
         ([Books].[BookName] LIKE '%'+@BookName+'%' OR @BookName IS NULL)
         AND  ([BookCategories].BookCategoryId = @BookCategoryId OR @BookCategoryId IS NULL)    
         AND  ([BookPublishers].BookPublisherId  = @BookPublisherId OR @BookPublisherId IS NULL) 
         AND  Books.IsActive = 1 
    ORDER BY 
         BookId 
    SELECT @TotalRecords = COUNT(BookId) FROM #TempBooks    
    SET @TotalRecords =  @TotalRecords
    SELECT *,  @TotalRecords AS TotalRecords FROM #TempBooks
    WHERE RowNumber between (@PageNumber - 1) * @PageSize + 1 and @PageNumber * @PageSize
    DROP TABLE #TempBooks
END
GO

SQL Server paging using limit and offset with non-unique ordering column

will SQL Server guerantee the correct data page when data is ordered by non-unique column?

No. Add the primary key columns to the ORDER BY to guarantee a stable ordering. EG

SELECT * FROM TableName
ORDER BY NonUniqueColumn, Id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY
OPTION (recompile)

---

Related Topics