Implement paging (skip / take) functionality with this query

sql

pagination

database-performance

best-practices

byAlex Kataev·Sep 13, 2024

Embrace paging using OFFSET to skip rows and FETCH NEXT to limit row count. Let's fetch the second page of a YourTable with 10-row pages for concreteness:

SELECT * FROM YourTable
ORDER BY YourColumn -- Care to join the order (of magnitude)?
OFFSET 10 ROWS -- skipping is a sport
FETCH NEXT 10 ROWS ONLY; -- don't be greedy!

Modify the YourColumn for sorting, and customize the offset and fetch values to match your desired page size and page number.

Dial down the chaos: ORDER BY

The ORDER BY clause is like your mom's reminder to clean up your room - it seems annoying but it's always right. You want your rows sorted before paging to avoid chaos. If you don't have any specific preference for ordering, be innovative and use CURRENT_TIMESTAMP.

Version-specific pagination

SQL Server 2012 and later

With SQL Server 2012 and later versions, OFFSET and FETCH NEXT are the go-to for paging. Just remember to not be rebellious and always include an ORDER BY before using them.

Earlier versions of SQL Server

For SQL Server versions released before 2012, OFFSET and FETCH NEXT are like unaffordable luxury brands. No worries though, we can get equally good results using the ROW_NUMBER() function and a Common Table Expression (CTE) trick:

WITH OrderedRows AS (
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn) AS RowNumber, *
    FROM YourTable
)
SELECT *
FROM OrderedRows
WHERE RowNumber BETWEEN 11 AND 20; -- We love inclusivity, don't we?

Alternate pagination roads

Paging using ROW_NUMBER()

When OFFSET and FETCH are not at your disposal, for cases where you feel like going against the trend or in scenarios where they simply can't be used, you can leverage the ROW_NUMBER() function:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn) as RowNum, *
    FROM YourTable
) AS RowsWithNumbers
WHERE RowNum > 10 and RowNum <= 20;

Remember though, even if it feels like a hipster alternative, it can slow ride the performance a bit.

Avoid nested queries with NOT IN

While it may feel like going down the rabbit hole, avoid using nested queries with NOT IN. They can be genuine resource hogs and pull down performance as the dataset size increases.

Extra tips on the house!

Not a TOP fan

Database is not a popularity game, so avoid using the TOP clause for paging. It might look cool and stylish, but it lacks the sophistication to let you specify a starting point for the result set.

Make friends with Indexing

Indexing is like a map when you are in a jungle (ahem! large database). Indexed columns aid in ordering and filtering data, hence improving your paging performance.

Maintain order in disarray

If ordering is impossible to establish with context, as your last resort, brave your fears and use ORDER BY (SELECT NULL). However, beware of those improper results lurking around the corner, just like bacteria on a public doorknob!