Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Functions and Triggers: Scalar UDF Inlining, Inline TVFs, Multi-Statement TVFs and Trigger Pitfalls
A scalar UDF that isn't inlined runs as a separate module per row: in a query over 17,359 orders the function was called 34,718 times, 365,498 logical reads never showed up in STATISTICS IO, and the plan couldn't go parallel. SQL Server 2019 inlining, is_inlineable, inline TVFs versus the fixed 100-row MSTVF estimate and interleaved execution, the single-row trigger assumption, EF Core's OUTPUT restriction and temporal tables for auditing, measured.
Stored Procedures: Parameters, Error Handling, Ownership Chaining and Safe Dynamic SQL
The right stored procedure skeleton is SET NOCOUNT ON, SET XACT_ABORT ON, ROLLBACK inside TRY/CATCH and a parameterless THROW; otherwise a CHECK error can commit half an order. Reading output parameters and return codes from Dapper, ownership chaining that gives the application user only EXECUTE, safe dynamic SQL with an allowlist, QUOTENAME and sp_executesql, and repairing the chain dynamic SQL breaks with module signing, with real output.
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.
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.
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.
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.
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.
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.
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.
Subqueries and Set Operators: Correlated Subqueries, EXISTS, the NOT IN Trap and UNION/INTERSECT/EXCEPT
A subquery uses one query's result inside another; NOT IN returns no rows at all if the list holds a single NULL. We cover scalar subqueries and error 512, IN/ANY/ALL, correlated subqueries, derived tables, EXISTS versus NOT EXISTS, and UNION, UNION ALL, INTERSECT and EXCEPT on Bookstore data with real output, logical reads and plan comparisons.
GROUP BY and Aggregate Functions: HAVING, Conditional Aggregation, GROUPING SETS and ROLLUP
GROUP BY collapses rows into groups, aggregates ignore NULL, and in SQL Server AVG over an integer column returns an integer. We show COUNT(*) versus COUNT(column), where WHERE and HAVING belong, STRING_AGG, conditional aggregation with CASE, a PIVOT comparison, and ROLLUP, CUBE, GROUPING SETS with GROUPING() on Bookstore data with real output.
SQL JOINs 2: Self Join, Non-Equi Join, Semi/Anti Join and CROSS APPLY vs LATERAL
Joining an order's items and payments together multiplies the totals (fan-out); the fix is to group first and join afterwards. We show CROSS JOIN report skeletons, a self join on the category tree, non-equi range joins, semi joins with EXISTS, anti joins with NOT EXISTS (same plan as LEFT JOIN … IS NULL), and every customer's last three orders with T-SQL CROSS/OUTER APPLY versus standard LATERAL in PostgreSQL and MySQL, with real output.