T-SQL by Example

T-SQL by Example

Welcome to T‑SQL by Example. Each page shows a bite‑sized example with runnable code, output (when helpful), and a few notes & gotchas.

  • Start with Basics, then explore Joins, Aggregation, and more.
  • Code samples target Microsoft SQL Server (2016+ features are noted).

Basics

  1. Hello, SELECT — Selecting constants and a first query.
  2. Columns & Aliases — Selecting specific columns, aliasing, schema-qualified names.
  3. WHERE Filters — Common predicates, BETWEEN/IN/LIKE/IS NULL and sargability notes.
  4. ORDER BY & TOP — Sorting, TOP, WITH TIES, NULLS LAST workaround.
  5. CASE Expressions — Simple vs searched CASE, default branch, common patterns.
  6. NULLs & 3-Valued Logic — NULL behavior, COALESCE/ISNULL, comparisons and pitfalls.
  7. 🚧 DISTINCT & Deduping — Use DISTINCT and ROW_NUMBER to remove duplicates.
  8. 🚧 Subqueries — Scalar, correlated, and table subqueries.
  9. 🚧 EXISTS vs IN — Compare semi-joins with EXISTS and IN.
  10. 🚧 CTEs — Common Table Expressions, basic and recursive.
  11. 🚧 Set Operators — UNION, INTERSECT, and EXCEPT patterns.
  12. 🚧 CROSS JOIN & Self Join — Cartesian products and self-joins.

Joins & APPLY

  1. INNER JOIN — Join two tables with matching keys; basic patterns.
  2. OUTER JOINs — LEFT/RIGHT/FULL joins, handling unmatched rows.
  3. CROSS/OUTER APPLY — Per-row subqueries/TVFs; choose CROSS vs OUTER APPLY.
  4. 🚧 Join Strategies — Hash, merge, and nested loops — recognition & when they appear.
  5. 🚧 Anti-join Patterns — NOT EXISTS vs LEFT JOIN/IS NULL.

Aggregation

  1. GROUP BY & Aggregates — Compute counts and sums; HAVING for post-aggregate filters.
  2. 🚧 HAVING vs WHERE — Pre-aggregate vs post-aggregate filtering.
  3. 🚧 Window Basics — ROW_NUMBER, RANK, DENSE_RANK.
  4. 🚧 Window Aggregates — SUM() OVER, running totals, moving averages.
  5. 🚧 Window Frames — ROWS vs RANGE, peers, gaps & islands.
  6. 🚧 Grouping Sets — ROLLUP, CUBE, and GROUPING SETS.

Data Modification

  1. 🚧 INSERT Patterns — Single row, multi-row, INSERT…SELECT, SELECT INTO.
  2. 🚧 UPDATE with JOIN & OUTPUT — Join in UPDATE; capture changes with OUTPUT.
  3. 🚧 DELETE Patterns — Delete with WHERE/JOIN; soft delete approaches.
  4. 🚧 MERGE (safe upserts) — Upserts with MERGE and caveats.
  5. 🚧 Identity & Sequences — IDENTITY columns and SQL Server SEQUENCE.

Programmability

  1. 🚧 Variables & Expressions — DECLARE, SET vs SELECT assignment, expressions.
  2. 🚧 Stored Procedures — Input/output params, RETURN, OUTPUT.
  3. 🚧 Functions: Scalar vs TVF — Scalar UDF, inline TVF, multi-statement TVF (perf notes).
  4. 🚧 TVPs (Table-Valued Parameters) — Table types and passing sets to procedures.
  5. 🚧 TRY…CATCH & XACT_STATE() — Error handling patterns for robust T-SQL.

Transactions & Concurrency

  1. 🚧 Transactions & Savepoints — BEGIN/COMMIT/ROLLBACK, savepoints.
  2. 🚧 Isolation Levels — RC, RCSI, RR, SR, SI — phenomena and trade-offs.
  3. 🚧 Deadlock Basics — Reproducing and diagnosing deadlocks.
  4. 🚧 Locking Hints — WITH (NOLOCK) and other hints — risks and uses.

Performance & Indexing

  1. 🚧 Sargability Deep Dive — Seek vs scan, avoid functions on indexed columns.
  2. 🚧 Index Basics — Clustered vs nonclustered, included columns.
  3. 🚧 Covering & Filtered Indexes — Cover hot queries; filter for selective subsets.
  4. 🚧 Statistics & Sniffing — Cardinality, auto-stats, parameter sniffing overview.
  5. 🚧 Execution Plans 101 — Operators, estimates vs actual, common shapes.
  6. 🚧 Temp Tables vs Table Variables — When to use #temp vs @table; indexing and stats.