Pagination and window functions solve different but related problems. Pagination splits large result sets into pages (think Google search results - 10 results per page). Window functions perform calculations across rows without collapsing them (think rankings, running totals). SQL Server uses OFFSET/FETCH for pagination (ANSI standard) instead of MySQL LIMIT. Window functions use the OVER clause to define calculation windows. Understanding both is essential for building performant applications.

**1. OFFSET/FETCH Pagination (SQL Server Standard)**

SQL Server uses OFFSET/FETCH instead of LIMIT. This is the ANSI SQL standard.

```sql

-- Basic pagination: Page 3, 10 items per page

SELECT

question_id,

title,

view_count,

created_at

FROM questions

ORDER BY created_at DESC

OFFSET 20 ROWS -- Skip first 20 rows (pages 1-2)

FETCH NEXT 10 ROWS ONLY; -- Get next 10 rows (page 3)

-- Calculate OFFSET from page number

DECLARE @PageNumber INT = 3;

DECLARE @PageSize INT = 10;

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

SELECT *

FROM questions

ORDER BY created_at DESC

OFFSET @Offset ROWS

FETCH NEXT @PageSize ROWS ONLY;

```

**Why this matters**: Stack Overflow uses OFFSET/FETCH to paginate 50M+ questions. ORDER BY is required - OFFSET/FETCH without ORDER BY is an error. FETCH NEXT is optional (OFFSET alone skips rows without returning any).

**Performance consideration**: OFFSET scans and skips rows. For page 1000 with 10 items per page, SQL Server scans 10,000 rows and returns 10. This is slow for deep pagination.

**2. Keyset Pagination (Seek Method)**

Keyset pagination uses WHERE clause instead of OFFSET. Much faster for deep pages.

```sql

-- Traditional OFFSET pagination (slow for deep pages)

SELECT question_id, title, created_at

FROM questions

ORDER BY created_at DESC

OFFSET 10000 ROWS -- Scans 10,000 rows

FETCH NEXT 10 ROWS ONLY;

-- Execution time: 500ms for page 1000

-- Keyset pagination (fast for any page)

-- First page: No WHERE clause

SELECT TOP 10

question_id,