Using LIMIT for pagination: SELECT * FROM products LIMIT 10

Use TOP: SELECT TOP 10 * FROM products ORDER BY product_id

SQL Server uses TOP, not LIMIT. Syntax: SELECT TOP n columns FROM table ORDER BY col. Alternative: OFFSET/FETCH (SQL Server 2012+): SELECT * FROM products ORDER BY product_id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY. TOP is simpler for first N rows. OFFSET/FETCH for pagination. Real-world: Stack Overflow uses TOP for initial page, OFFSET/FETCH for subsequent pages. Lesson: Use TOP in SQL Server, not LIMIT.

Use TOP for first N rows. Use OFFSET/FETCH for pagination. TOP is SQL Server syntax, LIMIT is MySQL/PostgreSQL.

High

Syntax error, LIMIT not supported in SQL Server

Not using columnstore indexes for analytics queries on large tables

Create columnstore index: CREATE COLUMNSTORE INDEX idx_name ON table (columns)

Columnstore indexes provide 10x faster analytics. How it works: Columnar storage (stores by column, not row), 5-10x compression, batch mode execution (processes 900 rows at once). Example: Sales aggregation without columnstore: 10 seconds. With columnstore: 1 second (10x faster). Use for: Data warehouses, analytics dashboards, reporting. Not for: OLTP (transactional workloads). Real-world: Power BI uses columnstore for fast dashboards. Lesson: Use columnstore for analytics.

Use columnstore for analytics (10x faster). Columnar storage + compression + batch mode. Not for OLTP. Real-world: Power BI uses columnstore.

High

Slow analytics queries (10x slower), poor dashboard performance

Using large OFFSET values for pagination: OFFSET 10000 ROWS

Use keyset pagination: WHERE id > @last_id ORDER BY id

Large OFFSET values are slow. Problem: OFFSET 10000 ROWS must skip 10000 rows (slow). Solution: Keyset pagination (cursor-based). Example: OFFSET: SELECT * FROM products ORDER BY id OFFSET 10000 ROWS FETCH NEXT 20 ROWS ONLY (slow, must skip 10000). Keyset: SELECT TOP 20 * FROM products WHERE id > @last_id ORDER BY id (fast, no skip). How it works: Remember last ID from previous page, query WHERE id > last_id. Real-world: Stack Overflow uses keyset pagination for questions. Lesson: Use keyset pagination for large datasets.

Use keyset pagination for large datasets. WHERE id > last_id (no offset). Fast for any page. Real-world: Stack Overflow uses keyset pagination.

Medium

Slow pagination (must skip 10000 rows), poor user experience