Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
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.
SQL JOINs 1: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN and the ON vs WHERE Difference
Joins match rows; they are not Venn diagrams: INNER JOIN keeps matched pairs, and LEFT/RIGHT/FULL OUTER JOIN also keep the preserved side's unmatched rows with NULLs. Filtering the right table of a LEFT JOIN in WHERE silently turns it into an INNER JOIN, which we see as Inner Join in the plan. We look at books without reviews, customers without orders, stock reconciliation with FULL OUTER JOIN and how one-to-many joins multiply rows, with real output.
Scalar Functions and CASE: Strings, Date/Time, Type Conversion and Sargability
Scalar functions are handy, but wrapped around a column in WHERE they make a query non-sargable: on 200,000 rows a YEAR()/MONTH() filter took 402 logical reads, the same month asked with a half-open range took 21. We cover CONCAT/CONCAT_WS, LEN versus DATALENGTH, CAST/CONVERT/TRY_CAST, how an nvarchar parameter loses the index through CONVERT_IMPLICIT, DATEDIFF counting boundaries, and the type and ELSE traps of CASE.