How to Paginate Results in SQL Server

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

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.

Query to insert row number to paginate results

Wrap your query up in a derived table (i.e. the subquery):

select * from
(
  Select ROW_NUMBER() OVER (ORDER BY reputation) as row, *
  From users
  Where reputation > 1000000
) dt
where   row >= ##StartRow:INT?1##
  AND   row    <= ##EndRow:INT?50000##
ORDER BY    row

Note that row is a reserved word according to the ANSI/ISO SQL standard, so you may need to delimit that column name as "row". (Or change it to something else.)

SQL Server query with pagination and count

Assuming you are using MSSQL 2012, you can use Offset and Fetch which cleans up server-side paging greatly. We've found performance is fine, and in most cases better. As far as getting the total column count, just use the window function below inline...it will not include the limits imposed by 'offset' and 'fetch'.

For Row_Number, you can use window functions the way you did, but I would recommend that you calculate that client side as (pagenumber*pagesize + resultsetRowNumber), so if you're on the 5th page of 10 results and on the third row you would output row 53.

When applied to an Orders table with about 2 million orders, I found the following:

FAST VERSION

This ran in under a second. The nice thing about it is that you can do your filtering in the common table expression once and it applies both to the paging process and the count. When you have many predicates in the where clause, this keeps things simple.

declare @skipRows int = 25,
        @takeRows int = 100,
        @count int = 0

;WITH Orders_cte AS (
    SELECT OrderID
    FROM dbo.Orders
)

SELECT 
    OrderID,
    tCountOrders.CountOrders AS TotalRows
FROM Orders_cte
    CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

SLOW VERSION

This took about 10 sec, and it was the Count(*) that caused the slowness. I'm surprised this is so slow, but I suspect it's simply calculating the total for each row. It's very clean though.

declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0


SELECT 
    OrderID,
    Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

CONCLUSION

We've gone through this performance tuning process before and actually found that it depended on the query, predicates used, and indexes involved. For instance, the second we introduced a view it chugged, so we actually query off the base table and then join up the view (which includes the base table) and it actually performs very well.

I would suggest having a couple of straight-forward strategies and applying them to high-value queries that are chugging.

Store results of SQL Server query for pagination

In SQL Server the ROW_NUMBER() function is great for pagination, and may be helpful depending on what parameters change between searches, for example if searches were just for different firstName values you could use:

;WITH search AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY firstName ORDER BY lastName) AS RN_firstName
                 FROM YourTable)
SELECT *
FROM search 
WHERE RN BETWEEN 51 AND 100
  AND firstName = 'John'

You could add additional ROW_NUMBER() lines, altering the PARTITION BY clause based on which fields are being searched.

SQL Server query for paging

The syntax error is occurring because you are not aliasing the subquery. You have to name an object that you are selecting from.

SELECT TOP 10 * FROM (
    SELECT TOP 100 [ID] ,[SUMMARY] ,[NAME]   FROM [db_test].[dbschema].[dborders] A ORDER BY id
)AS B ORDER BY ID

Also, as techspider points out below, the first error you will get will be due to a missing column list and the second error you should get will be from missing subquery alias.

One other point is that there are more optimized ways to implement paging in stored procedures. However, this answers your original question about a syntax error.

How can I efficiently paginate the results of a complex SQL query?

1) Create a composite index with the following order device_id and time desc.

2) Try to generate a query in this way

select device_1.time,
 (("device_1".value + "device_2".value) / "device_3".value) as value 
from raw_data as device_1 ,raw_data as device_2 ,raw_data as device_3 
where device_1.devise_id = 1 
and device_2.devise_id = 2 
and device_3.devise_id = 3 
and device_1.time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp 
and device_2.time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp 
and device_3.time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp 
and device_1.time = device_2.time 
and device_2.time = device_3.time

---

Related Topics