Explain the difference between OFFSET/FETCH and keyset pagination. When would you use each?

**OFFSET/FETCH Pagination**:

- Uses OFFSET n ROWS FETCH NEXT m ROWS ONLY

- Scans and skips rows: Page 1000 scans 10,000 rows

- O(n) complexity - performance degrades with page depth

- Supports arbitrary page jumps (go to page 50)

- Standard SQL syntax (ANSI)

**Keyset Pagination**:

- Uses WHERE clause: WHERE key > @last_value

- Seeks directly to position using index

- O(1) complexity - constant performance regardless of depth

- Only supports next/previous (no arbitrary page jumps)

- Requires unique, sequential key column

**When to use OFFSET/FETCH**:

- Need page numbers (go to page 50)

- Shallow pagination (pages 1-100)

- Small datasets (< 10K rows)

- Example: Stack Overflow question list with page numbers

**When to use Keyset**:

- Infinite scroll (next/previous only)

- Deep pagination (page 1000+)

- Large datasets (millions of rows)

- Example: Twitter feed, Facebook timeline

**Performance comparison**:

- OFFSET Page 1: 50ms, Page 1000: 5000ms

- Keyset Page 1: 5ms, Page 1000: 5ms (1000x faster)

**Real-world**: Twitter uses keyset for 500M+ tweets. Stack Overflow uses OFFSET/FETCH for page numbers. Choose based on UI requirements (page numbers vs infinite scroll) and dataset size.

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK? Provide examples.

**ROW_NUMBER**: Unique sequential numbers, no ties

- Syntax: ROW_NUMBER() OVER (ORDER BY column)

- Behavior: 1, 2, 3, 4, 5 (always unique)

- Use case: Pagination, unique row identification

**RANK**: Same rank for ties, gaps after ties

- Syntax: RANK() OVER (ORDER BY column)

- Behavior: 1, 2, 2, 4, 5 (gap at 4 after tie at 2)

- Use case: Leaderboards, Olympics rankings

**DENSE_RANK**: Same rank for ties, no gaps

- Syntax: DENSE_RANK() OVER (ORDER BY column)

- Behavior: 1, 2, 2, 3, 4 (no gap after tie)

- Use case: Top N per category, consecutive rankings