Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
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.
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.
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.
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.
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.
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.