Stack Overflow Question Pagination with OFFSET/FETCH

intermediate

Stack Overflow displays 50M+ questions with pagination. Users can navigate pages, sort by newest/votes/activity. The system needs to support page numbers (go to page 50) and maintain consistent ordering. OFFSET/FETCH provides standard SQL pagination with ORDER BY enforcement.

-- Stack Overflow question pagination

-- Page 3, 15 questions per page, sorted by newest

DECLARE @PageNumber INT = 3;

DECLARE @PageSize INT = 15;

DECLARE @Offset INT = (@PageNumber - 1) * @PageSize; -- (3-1) * 15 = 30

-- Get questions for page 3

SELECT

question_id,

title,

view_count,

answer_count,

score,

created_at,

user_name

FROM questions q

JOIN users u ON q.user_id = u.user_id

WHERE q.deleted_at IS NULL

ORDER BY q.created_at DESC

OFFSET @Offset ROWS

FETCH NEXT @PageSize ROWS ONLY;

-- Get total count for pagination UI (total pages)

SELECT COUNT(*) AS total_questions

FROM questions

WHERE deleted_at IS NULL;

-- Result: 50,000,000 questions

-- Total pages: 50,000,000 / 15 = 3,333,333 pages

-- Pagination with multiple sort options

DECLARE @SortBy VARCHAR(20) = 'votes'; -- 'newest', 'votes', 'activity'

SELECT

question_id,

title,

view_count,

answer_count,

score,

created_at,

last_activity_at

FROM questions