The json data type (SQL Server 2025)

In one line: SQL Server 2025’s json type validates documents on the way in and costs less to read than JSON in nvarchar(max). But the new JSON index serves JSON_CONTAINS, not the JSON_VALUE(...) = filters most code uses. For one property, an indexed computed column is still faster and much smaller.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/json-native-type on GitHub.

What you’ll see

Broken JSON sitting in nvarchar(max) columns. A truncated document was accepted there. The json type rejected it with error 13609.
A JSON index that the query ignores. WHERE JSON_VALUE(doc, '$.status') = N'held' scanned the whole table, 8,869 reads, with a JSON index on that exact path.
Error 13636 (“The JSON data type cannot be compared or sorted”) after a column changes from nvarchar(max) to json.
Documents that come back different. Whitespace is gone, and a duplicate key is dropped without an error.

Why it happens

nvarchar(max) is text. Every JSON_VALUE parses it again, and nothing checks that it’s JSON unless there’s a CHECK (ISJSON(doc) = 1) constraint. json stores a parsed, binary document, so it validates once and reads cheaply: 248 ms of CPU against 346 ms for the same filter over 200,000 rows. It keeps the document, not the text, so formatting and duplicate keys don’t survive.

The JSON index indexes paths inside the document, and in the tested build the optimizer used it for JSON_CONTAINS (644 reads instead of 8,869) but not for JSON_VALUE equality. It’s also large: 2,747 pages for one path, against 699 for an ordinary index on a computed column. Inserts into the table wrote 53% more log.

What to do

Store new JSON columns as json. You get validation and cheaper reads.
Before converting an existing column, search the code for comparisons, ORDER BY, GROUP BY and DISTINCT on it. They fail on json. Compare a property with JSON_VALUE, or cast to nvarchar(max) first.
For an equality filter on one property, use a computed column on JSON_VALUE with an index on it. In the demo it answered in 4 reads.
Use a JSON index for JSON_CONTAINS, such as searching arrays or many paths, and only after rewriting the queries to use it.

What doesn’t help

Adding a JSON index to speed up existing JSON_VALUE filters. They don’t use it.
Relying on json to keep the text exactly as sent. If something needs the original text, store that separately.

Check your server

-- SQL Server 2025+: json columns and JSON indexes in this database, and
-- nvarchar(max) columns that have an ISJSON check (likely JSON stored as text).
SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id)        AS table_name,
       c.name                          AS column_name,
       TYPE_NAME(c.user_type_id)       AS type_name
FROM sys.columns AS c
WHERE TYPE_NAME(c.user_type_id) = 'json'
  AND OBJECTPROPERTY(c.object_id, 'IsUserTable') = 1;

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, p.path
FROM sys.indexes AS i
JOIN sys.json_index_paths AS p
  ON p.object_id = i.object_id AND p.index_id = i.index_id;

SELECT OBJECT_NAME(cc.parent_object_id) AS table_name, cc.name, cc.definition
FROM sys.check_constraints AS cc
WHERE cc.definition LIKE '%isjson%';

See it happen

The json-native-type demo puts the same 200,000 order documents in nvarchar(max), in json, in json with a JSON index, and in nvarchar(max) with an indexed computed column. It measures validation, storage, read CPU, which queries use which index, and the log an insert writes. On 2022 and earlier it reports SKIPPED.

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.