**1. OFFSET/FETCH Performance**: OFFSET scans and skips rows. Page 1: fast (scan 10 rows). Page 1000: slow (scan 10,000 rows). Use keyset pagination for deep pages.

**2. Index Requirements**: ORDER BY columns must be indexed. Without index, SQL Server sorts entire table (slow). Composite index on (order_column, select_columns) is ideal.

**3. Keyset Pagination**: Constant performance regardless of page depth. Page 1: 5ms. Page 1000: 5ms. Trade-off: Cannot jump to arbitrary pages.

**4. Window Functions**: Window functions are calculated after WHERE and GROUP BY. They do not prevent index usage on base query. However, complex window frames can be expensive.

**5. PARTITION BY Performance**: Each partition is sorted separately. Many partitions with large datasets can be slow. Index on (partition_column, order_column) helps.

**6. ROW_NUMBER for Pagination**: Using ROW_NUMBER in CTE for pagination is slower than OFFSET/FETCH for simple cases. Use ROW_NUMBER when you need complex filtering or ranking before pagination.

**7. Covering Indexes**: Create covering indexes that include all columns in SELECT, WHERE, ORDER BY, and PARTITION BY. This avoids key lookups and improves performance by 10-100x.

OFFSET scans and skips rows - Page 1: fast, Page 1000: slow. Use keyset pagination for deep pages.

Keyset pagination has constant performance - Page 1: 5ms, Page 10000: 5ms (100x faster than OFFSET for deep pages)

Covering indexes eliminate key lookups - Include all SELECT, WHERE, ORDER BY columns in index

Filtered indexes for soft deletes - CREATE INDEX ... WHERE deleted_at IS NULL (smaller, faster)

Window functions do not prevent index usage on base query - Optimize base query first, then add window functions

PARTITION BY with many partitions can be slow - Each partition sorted separately, consider materialized views

ROW_NUMBER pagination slower than OFFSET/FETCH for simple cases - Use OFFSET/FETCH unless you need complex filtering

Avoid COUNT(*) OVER() in paginated queries - Calculate total count separately to avoid scanning all rows per page

Using TOP for pagination - TOP without ORDER BY is non-deterministic, use OFFSET/FETCH instead

Large OFFSET values - OFFSET 100000 scans 100K rows, use keyset pagination for deep pages

Missing tie-breaker in ORDER BY - Causes inconsistent ordering when sort column has duplicates

Not indexing ORDER BY columns - Causes full table sort (slow), always index ORDER BY columns

Using LIMIT syntax - SQL Server uses OFFSET/FETCH, not LIMIT (MySQL syntax)

Keyset pagination with non-unique columns - Must include unique tie-breaker (id) to handle duplicates

Complex window frames without indexes - Running totals on unindexed columns are slow

Using ROW_NUMBER for simple pagination - OFFSET/FETCH is faster and clearer for simple cases