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.
Incredibly, no other answer has mentioned the fastest way to do pagination in all SQL Server versions. Offsets can be terribly slow for large page numbers as is benchmarked here. There is an entirely different, much faster way to perform pagination in SQL. This is often called the "seek method" or "keyset pagination" as described in this blog post here.
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
The "seek predicate"
The @previousScore
and @previousPlayerId
values are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BY
direction is ASC
, simply use >
instead.
With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you're on page 4).
This is the best way to implement pagination when lazy loading more data in web applications, for instance.
Note, the "seek method" is also called keyset pagination.
Total records before pagination
The COUNT(*) OVER()
window function will help you count the number of total records "before pagination". If you're using SQL Server 2000, you will have to resort to two queries for the COUNT(*)
.
Finally, Microsoft SQL Server 2012 was released, I really like its simplicity for a pagination, you don't have to use complex queries like answered here.
For getting the next 10 rows just run this query:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned
Key points to consider when using it:
ORDER BY
is mandatory to useOFFSET ... FETCH
clause.OFFSET
clause is mandatory withFETCH
. You cannot useORDER BY ... FETCH
.TOP
cannot be combined withOFFSET
andFETCH
in the same query expression.