Using large OFFSET values for deep pagination

Use keyset pagination (WHERE clause) for deep pages

OFFSET pagination scans all skipped rows. For page 10000 with 10 items per page, SQL Server scans 100,000 rows and returns 10. This is O(n) complexity - performance degrades linearly with page depth.

Example - Deep pagination performance:

-- WRONG: OFFSET for page 10000 (scans 100K rows, 10 seconds)

SELECT question_id, title, created_at

FROM questions

ORDER BY created_at DESC

OFFSET 100000 ROWS -- Scans and skips 100,000 rows

FETCH NEXT 10 ROWS ONLY;

-- Execution plan: Index Scan (100,010 rows), 10 seconds

-- CORRECT: Keyset pagination (seeks to position, 10ms)

SELECT TOP 10

question_id,

title,

created_at

FROM questions

WHERE created_at < @LastCreatedAt -- Seek directly to position

OR (created_at = @LastCreatedAt AND question_id < @LastQuestionID)

ORDER BY created_at DESC, question_id DESC;

-- Execution plan: Index Seek (10 rows), 10ms

Keyset pagination has O(1) constant performance. Page 1: 10ms. Page 10000: 10ms. Trade-off: Cannot jump to arbitrary pages, only next/previous. Use OFFSET/FETCH for page numbers (1-100), keyset for infinite scroll (deep pages).

Use OFFSET/FETCH for shallow pagination (pages 1-100). Use keyset pagination for deep pagination or infinite scroll. Keyset provides 100-1000x better performance for deep pages.

High

OFFSET 100000 scans 100,000 rows and skips them, taking 10+ seconds

Missing tie-breaker column in ORDER BY

Always include unique column (id) as tie-breaker in ORDER BY

When ORDER BY column has duplicates, SQL Server can return rows in any order within the duplicate group. This causes inconsistent pagination - same row appears on multiple pages or skips pages entirely.

Example - Inconsistent pagination:

-- WRONG: No tie-breaker (inconsistent ordering)

SELECT question_id, title, created_at

FROM questions

ORDER BY created_at DESC -- Multiple questions have same created_at

OFFSET 10 ROWS

FETCH NEXT 10 ROWS ONLY;

-- Page 1: Questions A, B, C (all created_at = 2024-01-20 14:30:00)

-- Page 2: Questions B, C, D (B and C appear again!)

-- CORRECT: Include unique tie-breaker

SELECT question_id, title, created_at

FROM questions