T-SQL by Example
Browse all examples below.
Basics
- Hello, SELECT β Selecting constants and a first query.
- Columns & Aliases β Selecting specific columns, aliasing, schema-qualified names.
- WHERE Filters β Common predicates, BETWEEN/IN/LIKE/IS NULL and sargability notes.
- ORDER BY & TOP β Sorting, TOP, WITH TIES, NULLS LAST workaround.
- CASE Expressions β Simple vs searched CASE, default branch, common patterns.
- NULLs & 3-Valued Logic β NULL behavior, COALESCE/ISNULL, comparisons and pitfalls.
- π§ DISTINCT & Deduping β Use DISTINCT and ROW_NUMBER to remove duplicates.
- π§ Subqueries β Scalar, correlated, and table subqueries.
- π§ EXISTS vs IN β Compare semi-joins with EXISTS and IN.
- π§ CTEs β Common Table Expressions, basic and recursive.
- π§ Set Operators β UNION, INTERSECT, and EXCEPT patterns.
- π§ CROSS JOIN & Self Join β Cartesian products and self-joins.
Joins & APPLY
- INNER JOIN β Join two tables with matching keys; basic patterns.
- OUTER JOINs β LEFT/RIGHT/FULL joins, handling unmatched rows.
- CROSS/OUTER APPLY β Per-row subqueries/TVFs; choose CROSS vs OUTER APPLY.
- π§ Join Strategies β Hash, merge, and nested loops β recognition & when they appear.
- π§ Anti-join Patterns β NOT EXISTS vs LEFT JOIN/IS NULL.
Aggregation
- GROUP BY & Aggregates β Compute counts and sums; HAVING for post-aggregate filters.
- π§ HAVING vs WHERE β Pre-aggregate vs post-aggregate filtering.
- π§ Window Basics β ROW_NUMBER, RANK, DENSE_RANK.
- π§ Window Aggregates β SUM() OVER, running totals, moving averages.
- π§ Window Frames β ROWS vs RANGE, peers, gaps & islands.
- π§ Grouping Sets β ROLLUP, CUBE, and GROUPING SETS.
Data Modification
- π§ INSERT Patterns β Single row, multi-row, INSERTβ¦SELECT, SELECT INTO.
- π§ UPDATE with JOIN & OUTPUT β Join in UPDATE; capture changes with OUTPUT.
- π§ DELETE Patterns β Delete with WHERE/JOIN; soft delete approaches.
- π§ MERGE (safe upserts) β Upserts with MERGE and caveats.
- π§ Identity & Sequences β IDENTITY columns and SQL Server SEQUENCE.
Programmability
- π§ Variables & Expressions β DECLARE, SET vs SELECT assignment, expressions.
- π§ Stored Procedures β Input/output params, RETURN, OUTPUT.
- π§ Functions: Scalar vs TVF β Scalar UDF, inline TVF, multi-statement TVF (perf notes).
- π§ TVPs (Table-Valued Parameters) β Table types and passing sets to procedures.
- π§ TRYβ¦CATCH & XACT_STATE() β Error handling patterns for robust T-SQL.
Transactions & Concurrency
- π§ Transactions & Savepoints β BEGIN/COMMIT/ROLLBACK, savepoints.
- π§ Isolation Levels β RC, RCSI, RR, SR, SI β phenomena and trade-offs.
- π§ Deadlock Basics β Reproducing and diagnosing deadlocks.
- π§ Locking Hints β WITH (NOLOCK) and other hints β risks and uses.
Performance & Indexing
- π§ Sargability Deep Dive β Seek vs scan, avoid functions on indexed columns.
- π§ Index Basics β Clustered vs nonclustered, included columns.
- π§ Covering & Filtered Indexes β Cover hot queries; filter for selective subsets.
- π§ Statistics & Sniffing β Cardinality, auto-stats, parameter sniffing overview.
- π§ Execution Plans 101 β Operators, estimates vs actual, common shapes.
- π§ Temp Tables vs Table Variables β When to use #temp vs @table; indexing and stats.