Mert Özen Further With Every Line

Blog

Mert Özen's blog posts on software development, backend, frontend, and DevOps.

Series
187 posts found
Reading Execution Plans 1: Estimated vs Actual Plans, Operators and Join Algorithms
Database PART 05

Reading Execution Plans 1: Estimated vs Actual Plans, Operators and Join Algorithms

An execution plan is read right to left, inside out; the first thing to check is not cost percentage but estimated versus actual rows and how many times each operator ran. Where cost percentage misleads in the actual plan of an EF Core Include query, seek/scan/lookup, the measured cost of nested loops, merge and hash joins on the same query, stream and hash aggregates, and going from 2,549 to 689 logical reads with two indexes.

8 Nov 2026 10 min 59
Statistics and Cardinality Estimation: Histograms, Density and the Cost of a Wrong Estimate
Database PART 04

Statistics and Cardinality Estimation: Histograms, Density and the Cost of a Wrong Estimate

The optimizer estimates row counts from statistics: if the value is a histogram step the estimate is exact, inside a step it is an average, and when unknown it falls back to the density vector. On 1 million Bookstore orders: reading the histogram, verifying the auto-update threshold (31,623 changes) by experiment, a plan 24 times more expensive due to the ascending key, CE models on correlated columns, and CE feedback.

8 Nov 2026 11 min 58
Nonclustered Index Design: Key Order, INCLUDE, Covering Indexes and Key Lookups
Database PART 03

Nonclustered Index Design: Key Order, INCLUDE, Covering Indexes and Key Lookups

A good nonclustered index puts equality columns before the range column in the key and carries the query's remaining columns with INCLUDE; otherwise key lookup cost makes the optimizer scan the table. Bookstore's order search query drops from 7,707 to 25 logical reads; with the tipping point, how filtered indexes interact with EF Core literals, computed columns, HasIndex equivalents and write cost measured.

8 Nov 2026 12 min 52
Pages, Heaps and Clustered Indexes: How SQL Server Stores Data on Disk
Database PART 02

Pages, Heaps and Clustered Indexes: How SQL Server Stores Data on Disk

SQL Server keeps data in 8 KB pages; a clustered index is a shallow B-tree that is the table itself, while a heap means unordered pages and forwarded records on update. Real pages, ROW_OVERFLOW/LOB, choosing a clustering key, an experiment showing Guid.CreateVersion7 values are as random as NEWID in SQL Server ordering, and fill factor and page density measurements.

8 Nov 2026 13 min 54
A Query's Journey Inside SQL Server: Relational Engine, Storage Engine, Buffer Pool and Transaction Log
Database PART 01

A Query's Journey Inside SQL Server: Relational Engine, Storage Engine, Buffer Pool and Transaction Log

A query reaches SQL Server over TDS, is parsed, bound and optimized in the Relational Engine and lands in the plan cache; the Storage Engine reads pages through the buffer pool, and changes go to the transaction log first (WAL) and to the data file later via checkpoint. Also: the deterministic 5.6-million-row Bookstore lab data used across the series, the 2025 editions and how compatibility level differs from engine version.

8 Nov 2026 13 min 43
What SQL Server 2025 Added to T-SQL: REGEXP Functions, the Native JSON Type, the || Operator, PRODUCT and CURRENT_DATE
Database PART 18

What SQL Server 2025 Added to T-SQL: REGEXP Functions, the Native JSON Type, the || Operator, PRODUCT and CURRENT_DATE

SQL Server 2025 brought RE2-based REGEXP_LIKE/REPLACE/SUBSTR/INSTR/COUNT/MATCHES/SPLIT_TO_TABLE, a native json type, JSON_ARRAYAGG/JSON_OBJECTAGG, the || operator, PRODUCT, CURRENT_DATE, UNISTR and BASE64. With real output we show regex ignoring collation, an index seek for an anchored pattern, the preview fuzzy functions, and REGEXP_LIKE requiring compatibility level 170.

8 Nov 2026 13 min 41
What SQL Server 2022 Added to T-SQL: IS DISTINCT FROM, GREATEST/LEAST, WINDOW, GENERATE_SERIES, DATE_BUCKET and JSON Functions
Database PART 17

What SQL Server 2022 Added to T-SQL: IS DISTINCT FROM, GREATEST/LEAST, WINDOW, GENERATE_SERIES, DATE_BUCKET and JSON Functions

SQL Server 2022 brought IS DISTINCT FROM for NULL-safe comparison, GREATEST/LEAST, GENERATE_SERIES, DATETRUNC, DATE_BUCKET, TRIM with characters, ordered STRING_SPLIT, JSON_OBJECT/JSON_ARRAY and APPROX_PERCENTILE_CONT. We try each with before/after code and show with real error messages why GENERATE_SERIES, WINDOW and TRIM LEADING fail at compatibility level 150.

8 Nov 2026 12 min 29
One Query, Four Dialects: Differences Between SQL Server, PostgreSQL, MySQL and SQLite
Database PART 16

One Query, Four Dialects: Differences Between SQL Server, PostgreSQL, MySQL and SQLite

The same Bookstore queries give different syntax and different results on SQL Server, PostgreSQL, MySQL and SQLite: in MySQL || is an OR and 7 / 2 is 3.5000, and PostgreSQL is case-sensitive. We compare paging, string concatenation, identity and RETURNING, upserts, date functions, quoting, booleans, NULL ordering and LATERAL on four engines with real output.

8 Nov 2026 14 min 35
INSERT, UPDATE, DELETE and MERGE: OUTPUT, Upserts and Working Safely Against an UPDATE Without WHERE
Database PART 15

INSERT, UPDATE, DELETE and MERGE: OUTPUT, Upserts and Working Safely Against an UPDATE Without WHERE

T-SQL's UPDATE ... FROM raises no error when several source rows match one target row, and which value it writes is undefined; MERGE stops with error 8672. We cover multi-row INSERT, new ids with OUTPUT, the standard correlated UPDATE, the log difference between DELETE and TRUNCATE, MERGE traps, an explicit upsert and safe working with a @@ROWCOUNT check, with real output.

8 Nov 2026 14 min 33
Window Functions 2: Running Totals, ROWS vs RANGE Frames, LAG/LEAD and Gaps and Islands
Database PART 14

Window Functions 2: Running Totals, ROWS vs RANGE Frames, LAG/LEAD and Gaps and Islands

The default frame of a window aggregate with ORDER BY is RANGE UNBOUNDED PRECEDING; rows with equal values get the same total and SQL Server uses a worktable. We show running totals, the logical-read difference between ROWS and RANGE, moving averages with missing days, LAG/LEAD, the LAST_VALUE trap, medians with PERCENTILE_CONT, and gaps and islands on Bookstore data with real output.

8 Nov 2026 16 min 40
Window Functions 1: OVER, PARTITION BY, ROW_NUMBER, RANK and Top-N per Group
Database PART 13

Window Functions 1: OVER, PARTITION BY, ROW_NUMBER, RANK and Top-N per Group

A window function computes like GROUP BY but does not collapse rows; it writes the result next to each row. We show OVER and PARTITION BY, how ROW_NUMBER, RANK, DENSE_RANK and NTILE differ on ties, why they cannot appear in WHERE, top-N per group with a CTE, the tie decision, and safe duplicate removal with ROW_NUMBER on Bookstore data with real output.

8 Nov 2026 14 min 36
CTEs, Views and Recursive Queries: Readable SQL with WITH and Hierarchical Data
Database PART 12

CTEs, Views and Recursive Queries: Readable SQL with WITH and Hierarchical Data

A CTE lets you write a query top to bottom in named steps, but SQL Server does not materialize it; every reference runs again. We show chained CTEs, views, the SELECT * trap, SCHEMABINDING and WITH CHECK OPTION, a recursive CTE walking the category tree, cycle protection and MAXRECURSION on Bookstore data with real output and logical reads.

8 Nov 2026 18 min 43
Blog Map