Pages

Topics

Tools

↑↓navigate↵openescclose

SQL guides

Every published SQL guide, newest first, with practical examples you can copy into your own project.

3,815 guides across 14 topics

Topics

Latest SQL guides

3D illustration of a slow single-row cursor loop beside a wide parallel set pipeline processing many rows at once
SQL

SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance

Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.

SQLQuery OptimizationPerformance
3D illustration of a clock and calendar grid linked by interval arrows crossing time zone meridians with a DST marker
SQL

SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST

timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.

SQLDate and TimeBest Practices
3D illustration of database rows guarded by lock markers while worker queues skip locked rows and claim free ones
SQL

SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT

How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.

SQLTransactionsConcurrency
3D illustration of text tokens passing through concatenation blocks, substring blades and pattern filters into an output row
SQL

SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching

The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.

SQLString FunctionsSyntax
3D illustration of hierarchical JSON and JSONB data trees indexed by GIN path operators and query filters
SQL

SQL JSON and JSONB: Functions, Operators, and Indexing Strategies

Why jsonb is the default for queryable documents, when containment can use a GIN index, and the expression-index fixes for scalar lookups.

SQLJSONDatabase
3D illustration of tabular data rows rotating into columnar aggregates and back with a 3D coordinate matrix
SQL

SQL PIVOT and UNPIVOT: Row-to-Column and Column-to-Row Data Transformation

The literal column list, the NULL rows that UNPIVOT drops, and the type coercion that turns dates into strings — plus the portable FILTER form.

SQLData TransformationQueries
3D illustration of an analytical query cost tree with sequential and index scan nodes carrying execution time badges
SQL

SQL EXPLAIN and EXPLAIN ANALYZE: Reading Query Execution Plans for Optimization

Estimated rows versus actual rows is the whole game: reading plans, multiplying by loops, interpreting buffers, and fixing the statistics first.

SQLQuery OptimizationPerformance
3D illustration of automated event tripwires firing BEFORE and AFTER insert and update into an audit logging pipeline
SQL

SQL Triggers: BEFORE, AFTER, INSTEAD OF Syntax and Common Pitfalls

A BEFORE trigger that returns NULL deletes the row without an error, bulk updates fire it once per row, and TRUNCATE skips it entirely.

SQLTriggersAutomation
3D illustration of a procedural batch execution workflow contrasted with deterministic scalar and table-valued function pipes
SQL

SQL Stored Procedures vs Functions: Syntax, Parameters, and Execution Differences

Procedures act, functions compute: transaction control, IN/OUT parameters, volatility that lies, and the plan caching that makes procedures slow.

SQLDatabase FunctionsStored Procedures
3D illustration of a virtual dynamic query view compared with disk-cached materialized view data blocks and a background refresh engine
SQL

SQL Views vs Materialized Views: Syntax, Refresh Strategies, and Performance

Why a view is inlined into your query while a materialized view is a cached table, how CONCURRENTLY refreshes work, and the RLS trap hidden in views.

SQLViewsPerformance