SQL Server 2025 CU4: Does JSON_CONTAINS Break Your Queries? Test Day 2

Before we dive into today's topic, if you missed my previous post:  SQL Server 2025 CU3 vs CU4: Has Microsoft Really Fixed the SESSION_CONTEXT Issue? Test Day 1 . 

👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.

SQL Server 2025 CU4: Does JSON_CONTAINS Break Your Queries? Test Day 2

In this post, I’ll show you why a seemingly minor fix to JSON_CONTAINS in SQL Server 2025 CU4 might suddenly break your production workloads. Don't let silent logic errors ruin your uptime. Here is how to test and bulletproof your code!

TL;DR

✔️ KB 5029490 limits the JSON_CONTAINS comparison mode strictly to 0 or 1 🛠️ ✔️ Mode 0 enforces an Exact Match, while Mode 1 allows Pattern Matching 🧩 ✔️ Parameterized queries face the highest risk of breaking changes upon upgrading to CU4 ⚠️

Hi SQL SERVER Guys,

We all know how important it is to make every second count when managing SQL Server environments. Unpredictable query execution is a DBA's worst nightmare . Today, for our Day 2 test, we are diving into a minor but critical update in SQL Server 2025 CU4 that fundamentally changes how the engine handles JSON validation. Let's dive into these game-changing features!

What it really is: The JSON_CONTAINS Fix

The JSON_CONTAINS function accepts an argument called "comparison mode". Before this fix (CU3 and earlier), the SQL Server engine was dangerously forgiving.

The Problem: You could pass "weird" values into the function, like JSON_CONTAINS(..., ..., 2) or -1. The result was undocumented behavior, inconsistent matching, and a high risk of silent bugs or regressions.

✔️ The Fix (CU4): Microsoft restricted the parameter to only two valid values:

  • 0 → Exact match (Default): The JSON must match perfectly. JSON_CONTAINS('{"a":1}', '{"a":1}', 0) → TRUE JSON_CONTAINS('{"a":1}', '{"a":"1"}', 0) → FALSE
  • 1 → Pattern match: More flexible, working similarly to a "contains / like" operator. JSON_CONTAINS('{"name":"Luca"}', '{"name":"Lu"}', 1) → TRUE

Why is this important? 

This fix eliminates undefined behaviors, makes the function 100% deterministic, and prevents bugs that are nearly impossible to trace. However, the real-world impact is that if you had legacy code passing a 2, it will now throw an error or force strict behavior. Microsoft made the API "strict"—less silent flexibility equals more predictability.

🧪 1. Test CU3 vs CU4 on JSON_CONTAINS

☝We don't just blindly follow what Microsoft says. We double-check everything and run benchmarks to see what the real benefits are.

The goal here is to verify the engine's behavior with invalid values and spot breaking changes. 

Run this script on your instances:

PRINT '=== TEST SQL SERVER 2025 CU - JSON_CONTAINS MODES ===';

DECLARE @json JSON = N'{
    "info": {
        "name": "Luca Biondi",
        "tags": ["sql", "performance", "server-2025"]
    }
}';

-- ========================================================
-- TEST 1: MODE 0 (Exact Match) - Default
-- ========================================================
SELECT 
    JSON_CONTAINS(@json, N'{"name": "Luca Biondi"}', '$.info', 0) AS ExactMatch_OK,
    JSON_CONTAINS(@json, N'{"name": "Luca"}', '$.info', 0) AS ExactMatch_Fail; 
    -- Il secondo darà 0 perché "Luca" non è uguale a "Luca Biondi"

-- ========================================================
-- TEST 2: MODE 1 (Pattern Match / Partial)
-- ========================================================
SELECT 
    JSON_CONTAINS(@json, N'"sql"', '$.info.tags', 1) AS PatternMatch_Array,
    JSON_CONTAINS(@json, N'{"name": "Luca Biondi"}', '$.info', 1) AS PatternMatch_Object;

-- ========================================================
-- TEST 2: MODE 2 ??????? (Pattern Match / Partial)
-- ========================================================
SELECT 
    JSON_CONTAINS(@json, N'"sql"', '$.info.tags', 1) AS PatternMatch_Array,
    JSON_CONTAINS(@json, N'{"name": "Luca Biondi"}', '$.info', 2) AS PatternMatch_Object;

What to expect:

  • 🔴 CU3 (Before Fix): Passing mode 2, -1, or 999 might work randomly, behave like 0 or 1, or yield inconsistent results. Highly non-deterministic.

  • 🟢 CU4 (After Fix): Modes 0 and 1 work perfectly. 
  • Any other value results in an explicit ❌ error. The behavior is finally stable.

  • Any values different than 0 and 1 bring the error message:
Msg 13692, Level 16, State 1, Line 32
The comparison_mode argument of JSON_CONTAINS must be 0 or 1.

💣 2. Borderline Cases (The Dangerous Ones)

Here are the scenarios that will bite you if you don't audit your code before upgrading:

  • 💣 Case 1 — The "Phantom" Mode: JSON_CONTAINS(@json, N'{"name":"Lu"}', 2). In CU3, this could accidentally act like a pattern match. In CU4, it throws an invalid argument error.
  • 💣 Case 2 — Silent Bug in Production: Passing a hardcoded variable DECLARE @mode INT = 2; worked (poorly) in CU3 but will instantly break everything in CU4.
  • 💣 Case 3 — Parameterization (The Sneakiest): If a stored procedure accepts @mode INT from an application, and the app sends a 2, CU3 handled it unpredictably. CU4 triggers a runtime error, potentially taking down your app.
  • 💣 Case 4 — Inconsistent Query Plans: In CU3, invalid modes could generate wildly different execution plans and mess with cardinality estimates, destroying performance. CU4 stops this at the gate.
  • 💣 Case 5 — Nested JSON + Pattern: Searching a nested structure with mode 2 yielded random TRUE/FALSE results in CU3. CU4 enforces strict, coherent logic.

👉 “CU4 fixes a subtle but dangerous issue in JSON_CONTAINS by enforcing strict comparison modes, eliminating undefined behavior that could previously lead to silent logic errors.”

🚀 My REAL Strategy

In my experience, you cannot blindly apply cumulative updates without auditing your codebase for undocumented parameter usage. Before deploying CU4, run a text search across your sys.sql_modules for JSON_CONTAINS. Pay special attention to stored procedures where the comparison mode is passed as a variable (@mode). Add a validation block (IF @mode NOT IN (0,1) THROW...) immediately so your application handles the exception gracefully instead of crashing abruptly at the query execution level.

Official Sources:

Relevant JSON & XML Performance Resources

🔗 1. JSON vs XML performance
👉 JSON vs XML Indexing – Which One Really Performs Better?
A direct comparison between JSON and XML with real-world benchmarks. Perfect for context when discussing JSON_CONTAINS.

🔗 2. Why XML is slower → JSON context
👉 Why Most Developers Should Stop Using XML in SQL Server!
Explains the architectural reasons why JSON is often the superior choice for modern SQL workloads.

🔗 3. Weekly recap
👉 SQL Server Performance Weekly Recap – What You Might Have Missed This Week!
A great resource for cross-linking that includes further references to JSON vs XML indexing.

🔗 4. SQL Server 2025 JSON improvements
👉 SQL Server 2025 is coming!
Discussion on upcoming JSON enhancements and how they relate to the latest Cumulative Update (CU) changes.

📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.

Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion.

Comments

Post a Comment

I Post più popolari

Speaking to Sql Server, sniffing the TDS protocol

Image
Hello friends!  If yesterday you read my post and found it definitely boring (for the topic of course and not for how it was written to the author 😁) well today I promise you that the topic will be much more interesting! Let's talk about Sniffing , let's talk about going to see what happens when we "log in" or when we send a Query to SQL Server.  We will do this by taking a look to the little known tabular data stream (TDS) protocol built on top of the TCP/IP protocol. Ready! The TDS protocol What is the Tabular data stream (TDS) protocol?  The TDS is the protocol used by the SQL Server client Net-Library to communicates with SQL Server . TDS is specific to SQL Server and it is a low-level protocol that specifies both commands and data in a specific arrangement.  TDS encapsulate the TCP/IP protocol. Sniffing TDS packets you can read from the network the requests that your application do to the SQL Server and read the answer .   How to Sniff data from the ne...
Read more

SQL Server, find text in a Trigger, Stored Procedures, View and Function. Two ways and what ways is better

Image
Hi guys, Welcome back! Today a light post about a question that has been asked to me several times. The question is: how to  search for text inside a trigger or a stored procedure ? We have two different ways. A first way using a system view called syscomments: SELECT obj.xtype, text , * FROM sysobjects obj JOIN syscomments com on obj.id = com.id WHERE TEXT like '%text_that_i_am_searching_for%' A second way using a system view called sql_module: SELECT o. name AS Object_Name, o. type_desc , m. definition FROM sys.sql_modules m JOIN sys.objects o ON m. object_id = o. object_id WHERE m. definition Like '%text_that_i_am_searching_for%' ;   Another question is? What differences do we have in using one way instead of the other? Which solution is better? You have to consider that the system view sys.syscomment is obsolete and will be removed sooner or later. So don't use it in production enviroiments. The sys.sql_m...
Read more

SQL Server, Avoid that damn Table Spool!

Image
Hi guys, Welcome back for another post! A light one inspired from what i do during my work: many times I optimize queries rewriting them or adding index and i like it very much! Sometimes you need to use a little imagination, but everything you learn ... sooner or later it will come in handy! Enjoy the reading mates! Avoid the Table Spool, when you can A few days ago I came across a query whose execution time was very high ... or so it was for the client complaining about the problem. Before recreating it, let's create the structures and fill the tables with some data Create table PriceList (Id int identity (1,1) primary Key ,Code Varchar (20)) Insert into PriceList (code) select top 1000 right( '0000' + cast ( row_number () over ( order by id) as varchar (4)),4) from artico Insert into PriceList (code) select top 4000 right( '1000' + cast ( row_number () over ( order by id) as varchar (4)),4) from artico Create table OrdersList (Id int identity...
Read more