SQL Server & T-SQL Cheat Sheet

Logical processing order

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/OFFSET.

Joins

INNER · LEFT · RIGHT · FULL · CROSS · SELF · CROSS APPLY · OUTER APPLY · anti-join (LEFT JOIN ... WHERE b.Id IS NULL or NOT EXISTS).

Window functions

sql

ROW_NUMBER()/RANK()/DENSE_RANK()/NTILE(n) OVER (PARTITION BY a ORDER BY b)
SUM(x) OVER (PARTITION BY a)                                  -- group total on every row
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)              -- running total
LAG(x, 1) / LEAD(x, 1) OVER (ORDER BY d)
FIRST_VALUE / LAST_VALUE

Must-know queries

Nth highest salary (DENSE_RANK) · duplicates (GROUP BY HAVING) · delete duplicates (CTE + ROW_NUMBER) · employee–manager (self join) · org chart (recursive CTE) · top N per group · running total · customers without orders (NOT EXISTS) · pivot (CASE/PIVOT) · upsert (MERGE).

Index rules

One clustered (narrow, unique, increasing) · non-clustered for selective filters · equality columns first, then range · INCLUDE to cover · filtered for subsets · columnstore for analytics · SARGable predicates · every index costs writes.

Isolation levels

READ UNCOMMITTED → READ COMMITTED (default) → REPEATABLE READ → SERIALIZABLE; SNAPSHOT and RCSI use row versions (readers don't block writers).

Error handling template

sql

SET NOCOUNT ON; SET XACT_ABORT ON;
BEGIN TRY
  BEGIN TRAN;
    -- work
  COMMIT;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK;
  THROW;
END CATCH

Handy functions

GETDATE() SYSUTCDATETIME() DATEADD DATEDIFF EOMONTH FORMAT CAST/CONVERT/TRY_CONVERT ISNULL/COALESCE/NULLIF IIF CHOOSE LEN SUBSTRING CHARINDEX REPLACE TRIM STRING_AGG STRING_SPLIT CONCAT_WS JSON_VALUE OPENJSON @@ROWCOUNT SCOPE_IDENTITY() NEWID() GREATEST/LEAST (2022).

Diagnostics

SET STATISTICS IO, TIME ON · actual execution plan · Query Store · sys.dm_exec_requests · sys.dm_exec_query_stats · sys.dm_db_index_usage_stats · sys.dm_db_missing_index_details · sp_WhoIsActive · Extended Events system_health for deadlocks.