I have been working with JSON in T-SQL since SQL Server added the functions in 2016. Storing a document in NVARCHAR(MAX) and reading values with JSON_VALUE is straightforward. Once queries start filtering on those values, I also have to decide how to index them. A few frequently searched properties are manageable. A growing list of them can mean more computed columns and indexes to maintain.

SQL Server 2025 adds a native JSON type and JSON indexes, both now generally available. I am interested in what they can do for those searches, especially where one document contains several properties the application needs to filter on.

Here is the text-based storage many of us have been using:

CREATE TABLE dbo.SettingsText (
    Id       INT IDENTITY(1,1) PRIMARY KEY,
    Payload  NVARCHAR(MAX) NOT NULL
);

That column accepts any string. Adding a CHECK constraint with ISJSON(Payload) = 1 lets SQL Server reject malformed documents on inserts and updates. Validating JSON was already possible; the document was still stored as text.

With the native JSON type, the table becomes:

CREATE TABLE dbo.Settings (
    Id       INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Payload  JSON NOT NULL
);

SQL Server stores the document in a parsed binary format. That can make reads more efficient because the engine does not have to keep parsing the original text. The type rejects malformed input and accepts objects and arrays at the top level, rather than standalone numbers or strings.

It cannot tell you whether a document contains everything your application needs, though. An empty object is valid JSON. If your application requires a theme setting or a customer ID, you still need to check for it.

For JSON stored as text, an indexed computed column is an established way to make searches faster. It extracts a property into a value SQL Server can index. That works well when you know which properties people search, and it remains worth considering in SQL Server 2025.

The new option is to index the JSON column directly:

SET QUOTED_IDENTIFIER ON;

CREATE JSON INDEX IX_Settings_Payload
    ON dbo.Settings (Payload);

This indexes paths throughout the document. If the searches only use one part of it, you can add a clause such as FOR ('$.preferences') to index that path and the values below it.

For example, the JSON index supports equality searches in this form:

SELECT Id
FROM dbo.Settings
WHERE JSON_VALUE(Payload, '$.preferences.theme') = 'dark';

Supporting a query does not guarantee the optimizer will choose the index. Check the actual execution plan. The possibility of covering several properties with one index appeals to me. Before replacing a computed-column index, I would compare both using the application's actual queries.

Before adding one to a production table, check the current restrictions:

• The table needs a clustered primary key. The example above defines one.
• Only one JSON index is allowed per JSON column.
• Index builds are offline and block access to the table while they run. Plan for that interruption.
• LIKE and IS NULL predicates are not currently supported by the JSON index.

The index example also sets QUOTED_IDENTIFIER to ON explicitly, so it does not depend on your client's default setting.

There are other JSON additions in this release. JSON_OBJECTAGG and JSON_ARRAYAGG build objects and arrays from rows, and JSON_CONTAINS searches within documents. JSON_MODIFY is not new; it has been available since 2016. The native type adds a modify() method that can update values in place in supported cases.

For an existing database, I would begin with a copy of the data and the queries that are expensive today. If the text column has no JSON validation, this query finds rows to investigate before conversion:

SELECT Id, Payload
FROM dbo.SettingsText
WHERE Payload IS NOT NULL
  AND ISJSON(Payload) = 0;

Without a type argument, ISJSON checks for an object or array. The query catches malformed text and standalone scalar values. Documents that pass still need conversion testing, including checks against the native type's limits and a comparison of the converted values.

A failed CAST can stop a bulk update, so test the conversion before running it against production. If you add a JSON column alongside the existing text column, keep incoming writes synchronized until the application switches over.

I would keep a computed-column index that already handles an important query well. Where several properties need indexing, a JSON index is worth testing against that existing design. Include inserts and updates in the comparison, along with query times, storage, and the time needed to build the index. I want to reduce the work of maintaining these tables, but the application still has to perform well while people are using it.