SQL Server

T-SQL as SQL Server shops actually write it — indexes, seeks and plans, window functions, MERGE, procedures, isolation and deadlocks — with every claim run on SQL Server 2022.

Start the course Practise this with real questions

In this course

Where T-SQL parts ways with standard SQLTOP and WITH TIES, OFFSET/FETCH, IDENTITY that never gives numbers back, and why the N in N'text' matters.8 min
NULL, three-valued logic and the T-SQL quirks around itWhy NOT IN can return nothing, how ISNULL truncates, why a UNIQUE column takes one NULL, and IS DISTINCT FROM in SQL Server 2022.9 min
Aggregates and grouping, the T-SQL wayInteger AVG, COUNT(*) against COUNT(col), WHERE against HAVING, ROLLUP with GROUPING(), and STRING_AGG in a fixed order.9 min
Joins that multiply rows, semi-joins, and APPLYWhy a join can double a SUM, EXISTS as a semi-join, and CROSS/OUTER APPLY for "the latest N per group".9 min
Window functions and the frame nobody specifiesROW_NUMBER, RANK and DENSE_RANK on ties, LAG and LEAD, and why a running total needs ROWS rather than the default RANGE frame.10 min
CTEs and recursive CTEsWhat a CTE is and is not, how a recursive CTE walks a hierarchy, and the MAXRECURSION limit of 100 that stops it.9 min
Normalization, functional dependencies and the constraints that hold them1NF to BCNF in terms of functional dependencies, the anomalies each removes, how to find violations in existing data, and when to denormalise.10 min
Clustered and nonclustered indexesWhat a clustered index really is, how a nonclustered index finds its row, key lookups and INCLUDE, and why only a leading prefix of a composite key can be sought.10 min
Seeks, scans and SARGable predicatesWhen a predicate can use an index seek, the rewrites that make a non-SARGable filter seekable, and the implicit conversion that quietly prevents one.10 min
Execution plans and the statistics behind themEstimated against actual plans, how to read operators and arrows, and the histogram the optimizer's row estimates come from.11 min
Query tuning: temp tables, catch-all filters and pagingTemp tables against table variables, the optional-filter query that cannot seek, and keyset paging instead of a deep OFFSET.11 min
MERGE, upserts and the ways they go wrongWhat MERGE does in one statement, the duplicate-source error, the NOT MATCHED BY SOURCE clause that deletes too much, and concurrent upserts.10 min
Stored procedures, safe dynamic SQL and parameter sniffingParameters, OUTPUT and return codes, sp_executesql against string concatenation, and what parameter sniffing is and how to deal with it.11 min
TRY/CATCH, THROW and XACT_ABORTWhat TRY/CATCH catches, THROW against RAISERROR, XACT_STATE, and why SET XACT_ABORT ON belongs at the top of every procedure that opens a transaction.11 min
Transactions, isolation levels, RCSI and SNAPSHOTNested transactions that are not nested, savepoints, the locking isolation levels, and what read committed snapshot and SNAPSHOT isolation change.12 min
Deadlocks, how SQL Server breaks them, and how to stop causing themWhat a deadlock is, how the victim is chosen, the three shapes that cause most of them, and the retry every caller needs anyway.11 min