New T-SQL syntax traps (SQL Server 2025)

In one line: SQL Server 2025’s || operator isn’t CONCAT. A NULL on either side makes the result NULL, and it has the same precedence as +. The new PRODUCT() aggregate is exact, but it returns the column’s type, so an int column overflows.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/tsql-2025-syntax on GitHub.

What you’ll see

  • A NULL where text was expected: 'id=' || @n is NULL when @n is NULL. CONCAT('id=', @n) returns id=.
  • Error 245 from 'total=' || 1 + 2: “Conversion failed when converting the varchar value ‘total=1’ to data type int.”
  • Error 402 from 1 || 2: two numbers can’t be concatenated.
  • A string cut at 8,000 characters, with no warning.
  • Error 8115, arithmetic overflow, from PRODUCT() on an int column.
  • Error 9844 from UNISTR on a varchar string.

Why it happens

|| follows the old + rules for NULLs and for length: one NULL makes the result NULL, and without a varchar(max) on one side the result stops at 8,000 characters. Where it differs from + is conversion: it turns the number into a string, so 'n=' || 5 works where 'n=' + 5 fails.

|| and + have the same precedence and run left to right. In 'total=' || 1 + 2, the concatenation runs first and gives 'total=1', and then + 2 tries to turn that into a number.

PRODUCT() returns the type of its input, like SUM(). Ten values of 10 in an int column already pass the int limit.

UNISTR on a varchar needs a UTF-8 collation to hold the characters it creates.

What to do

  • Keep CONCAT wherever a value can be NULL, or wrap each nullable part in ISNULL.
  • Put parentheses around arithmetic inside a || expression: 'total=' || (1 + 2).
  • Make one side varchar(max) when a result can pass 8,000 characters.
  • Replace EXP(SUM(LOG(x))) with PRODUCT(x). It’s exact, and it handles zero and negative numbers, which made LOG fail with error 3623.
  • Cast before PRODUCT(): PRODUCT(CAST(x AS bigint)) or decimal(38, n).
  • Use N'' literals with UNISTR.
  • Encode and decode with the same type in BASE64_ENCODE: varchar and nvarchar give different strings for the same text.

What doesn’t help

  • Swapping CONCAT for || everywhere for style. It changes the result whenever a value is NULL.
  • Raising the compatibility level. None of this syntax needs 170; it all ran at 160.

Check your server

-- Code in this database that uses the EXP(SUM(LOG())) trick, a candidate
-- for PRODUCT() once you're on SQL Server 2025, and the compatibility
-- level of each database.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS object_name
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%EXP(%SUM(%LOG(%';

SELECT name, compatibility_level
FROM sys.databases
ORDER BY name;

See it happen

The tsql-2025-syntax demo tests || against + and CONCAT for NULLs, conversion, precedence and truncation. It compares PRODUCT() with EXP(SUM(LOG())), checks CURRENT_DATE, SUBSTRING, DATEADD, UNISTR and BASE64_ENCODE, and runs the new syntax at compatibility level 160. On 2022 and earlier it reports SKIPPED.

Related: regular expressions · the json data type · implicit conversions

Measured on SQL Server 2025 RTM-CU9-GDR (17.0.5015.4), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights