SQL patterns that come up every week: CTEs, window functions, JSON, indexes, and the dialect quirks between engines.
How to split delimited strings into rows with STRING_SPLIT and OPENJSON, query JSON arrays stored in columns, and aggregate rows back into strings or arrays with STRING_AGG and JSON_ARRAYAGG.
T-SQL IDENTITY columns and SEQUENCE objects compared. SCOPE_IDENTITY vs @@IDENTITY vs OUTPUT INSERTED, the identity-cache gap-of-1000 after restarts, IDENTITY_INSERT, DBCC CHECKIDENT reseeding, CREATE SEQUENCE, NEXT VALUE FOR restrictions, and when to use which.
T-SQL stored procedures explained. CREATE OR ALTER, IN/OUTPUT parameters, table-valued parameters, return codes vs OUTPUT vs result sets, SET NOCOUNT ON, parameter sniffing and its fixes, the sp_ prefix trap, error handling with TRY/CATCH and THROW.
How table partitioning works in SQL Server. Covers partition functions and schemes, RANGE LEFT vs RIGHT, partition elimination, aligned indexes, SWITCH, sliding windows, and TRUNCATE WITH PARTITIONS.
How DML triggers work in SQL Server. Covers AFTER and INSTEAD OF triggers, the inserted and deleted virtual tables, the per-statement firing model, multi-row safety, nesting and recursion, and when not to use triggers at all.
How to read T-SQL execution plans. Covers estimated vs actual plans, SET STATISTICS XML, scan vs seek, key lookups, spills and implicit-conversion warnings, cardinality estimates, sys.dm_exec_query_plan_stats, and Query Store.
T-SQL #temp tables vs @table variables compared. Covers statistics and cardinality estimates, the 1-row legacy estimate and 2019 deferred compilation, transactions and rollback behavior, parallelism limits, indexes, recompiles, and a decision guide.
How to upsert in SQL Server. Covers the MERGE statement (all three WHEN clauses, the HOLDLOCK race condition, the honest state of its bug list), the UPDATE-then-INSERT pattern, and which to use.
How transactions work in SQL Server. Covers BEGIN/COMMIT/ROLLBACK, the XACT_ABORT trap, nested transaction behavior, savepoints, all five isolation levels, RCSI vs SNAPSHOT, and deadlock handling.
T-SQL subqueries explained. Covers scalar subqueries and error 512, IN vs EXISTS, the NOT IN NULL trap, correlated subqueries, derived tables and the mandatory alias, ANY/ALL/SOME, and when to rewrite a correlated subquery as APPLY.
T-SQL views explained. Covers CREATE OR ALTER VIEW, updatable views and WITH CHECK OPTION, the SELECT * schema-drift trap and sp_refreshview, ORDER BY restrictions, plus indexed views: SCHEMABINDING, COUNT_BIG, the definition restrictions, and the NOEXPAND hint on non-Enterprise editions.
T-SQL aggregate functions explained. Covers COUNT(*) vs COUNT(column), the SUM int overflow error, the AVG integer division trap, NULL elimination, HAVING vs WHERE, ROLLUP and GROUPING SETS, conditional aggregation without FILTER, and APPROX_COUNT_DISTINCT.
T-SQL string functions explained. Covers CONCAT vs the + operator NULL trap, LEN vs DATALENGTH, CHARINDEX and PATINDEX, STUFF, TRIM, STRING_SPLIT with ordinal, STRING_AGG truncation, collation case sensitivity, and the 2025 regex functions.
How to work with dates in SQL Server. Covers datetime2 vs datetime, GETDATE vs SYSDATETIME, DATEDIFF boundary counting, DATETRUNC and DATE_BUCKET (2022), AT TIME ZONE, FORMAT vs CONVERT, and sargable date filters.
How to set up and query full-text search in SQL Server. Covers full-text indexes and catalogs, CONTAINS vs FREETEXT, CONTAINSTABLE ranking, prefix-only wildcards, the stoplist trap, and async population.
How joins work in SQL Server. Covers INNER, LEFT, RIGHT, FULL, and CROSS joins, the ON vs WHERE trap on outer joins, anti-join patterns (NOT EXISTS vs NOT IN), CROSS APPLY and OUTER APPLY, and join hints.
How to pivot rows into columns in SQL Server. Covers the native PIVOT operator, conditional aggregation with CASE, UNPIVOT and its NULL-dropping behavior, CROSS APPLY VALUES, and dynamic pivots with sp_executesql.
How to use common table expressions in SQL Server. Covers WITH syntax, chaining CTEs, recursive CTEs for hierarchies, the MAXRECURSION 100 default, the semicolon quirk, and CTE vs temp table.
How to use window functions in SQL Server with OVER, PARTITION BY, and frame clauses. Covers ROW_NUMBER, RANK, LAG/LEAD, the RANGE default gotcha, and the 2022 WINDOW clause and IGNORE NULLS.
How data type choice drives compression and query speed in ClickHouse. Covers right-sizing integers, LowCardinality dictionary encoding and when it helps, Enum vs LowCardinality, the real cost of Nullable, FixedString, Date vs DateTime sizing, and avoiding String for everything.
How ClickHouse data skipping indexes work, the five index types (minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1), how GRANULARITY controls the index block size, when a skip index actually helps, and the correlation rule that decides whether it skips anything at all.
How to model key-value and repeated-group data in ClickHouse with Map(K, V), Nested, and Tuple. Covers m[k] access and its linear cost, mapKeys/mapValues subcolumns, mapContains, the Nested-is-parallel-arrays model, the flatten_nested setting, and when to reach for a Map versus a Nested versus parallel arrays.
A practical guide to the MergeTree family in ClickHouse: plain MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree and VersionedCollapsingMergeTree. How merges work, what each engine deduplicates or pre-aggregates, the eventual-consistency trap, and how to pick one.
How PARTITION BY works in ClickHouse: choosing a partition key, partition pruning, partition-level operations (DROP, DETACH, ATTACH, MOVE, FREEZE), the too-many-parts problem, and why most teams over-partition.