SQL Server 2025 finally gives developers a native JSON data type and, with it, a purpose-built way to index JSON documents with the new CREATE JSON INDEX statement. Before this, indexing JSON meant exposing individual properties through computed columns and building standard indexes on top.

It’s a major step toward closing the gap with databases like PostgreSQL, long praised for its JSON and JSONB support. However, as a preview feature, JSON indexing comes with real constraints DBAs and developers should understand before adopting it.

This guide has everything you need to know about JSON indexing in SQL Server 2025: what it is, how it works, and current limitations.

For many years, one of the reasons given as to why developers preferred PostgreSQL to SQL Server was the level of JSON (and more recently JSONB) support. From SQL Server 2017, that started to change.

JSON support in SQL Server pre-2025: what was it like?

In SQL Server 2017, several useful JSON-related functions were added:

If you’d worked with XML in SQL Server, this was all very familiar, aside from one key difference. With XML, we had an XML data type – but with JSON, there was not a JSON data type.

Why was there no JSON data type?

One of the key reasons for this was that, for a while, the product team weren’t keen on adding new data types because of the extra work required around the edges. For example, all the work required in client libraries and related code.

Another useful addition along the way was the ISJSON function, designed to test if the value was Internet Engineering Task Force (IETF) conformant JSON. In SQL Server 2022, it could check if the JSON was a valid VALUE, ARRAY, OBJECT, or SCALAR.

There were limitations in practical use – it didn’t, for example, check for key uniqueness, and while we could check if a value was JSON, we couldn’t actually store it as JSON.

Sub-queries were a challenge with this. If a sub-query returned JSON data, what data type would it actually return without a JSON data type? When XML had the same issue early on, the TYPE directive was effectively its replacement, saying: “I really meant XML”.

Another useful addition was the JSON_PATH_EXISTS function, which eliminated potential confusion when you were checking for JSON values. If you asked to retrieve a value and the path to the value didn’t exist, you just got NULL back. OK, you did also get NULL back if the value was defined in the JSON as NULL, but this function then let us work out what was happening.

Finally, in SQL Server 2022, we could construct JSON in T-SQL code by using new functions like JSON_ARRAY and JSON_OBJECT.

JSON indexing in SQL Server 2025

SQL Server 2025 changed everything for JSON, adding – finally – a JSON data type, along with the aggregates JSON_ARRAYAGG and JSON_OBJECTAGG.

The biggest change, however, was around indexing. Prior to SQL Server 2025, indexing JSON data generally meant exposing individual JSON properties through computed columns and then creating standard SQL Server indexes on those columns.

I must admit, it worked quite well – in fact, we’d done the same with XML decades ago. But, the approach introduced in SQL Server 2025 was far more direct: the JSON index, with the new CREATE JSON INDEX statement. This new statement can index values within a JSON document without requiring a computed column for every property that you want to search.

It’s worth noting that, at the time of writing, JSON indexes are still listed as a preview feature. As an aside, I really wish there weren’t so many features in SQL Server 2025 still in preview!

An example of JSON indexing in SQL Server 2025

Consider an application that stores additional customer information in JSON:

The ContactDetails column might contain documents such as:

SQL Server can retrieve individual properties with functions such as JSON_VALUE:

If we didn’t have a way to index this, SQL Server might need to examine all of the JSON data – and for every row.

As I mentioned, one solution was to create a computed column:

Note that I’ve used the PERSISTED option as I like these to just be calculated when the value is changed.

Indexing this way is still useful, particularly when JSON is stored in varchar or nvarchar columns. SQL Server can match a query containing the same JSON_VALUE expression to the indexed computed column without requiring applications to query the computed column explicitly.

How to create a JSON index

For a column using the new native JSON data type introduced in SQL Server 2025, we can create an index directly:

When you do this, SQL Server recursively indexes the paths in the JSON document. Queries like the one searching for Australian customers might then take advantage of the JSON index.

This is even more powerful when applications search several properties within a document. Instead of adding multiple computed columns and indexes, one JSON index can support searches across multiple JSON paths.

How to define paths to index

To minimize the impact of this data, we can redefine the index to only index the paths that our apps actually search:

Note that specifying $.contact recursively covers values below that path. As such, SQL Server doesn’t allow overlapping paths, like $.contact and $.contact.preferredMethod, in the same JSON index.

Limiting the indexed paths can make sense for large documents where only a small part of the JSON is used for searching.

Which queries can use JSON indexes?

Unlike other special indexes (such as spatial ones), JSON indexes aren’t limited to just one new JSON-specific predicate. They let you search JSON far more conveniently.

For example, a straightforward scalar comparison can use JSON_VALUE:

I mentioned that JSON_PATH_EXISTS can test whether a path is present:

Also added in SQL Server 2025 was JSON_CONTAINS. It searches for values, objects, or arrays within JSON documents and can take advantage of a JSON index.

A bit more on JSON arrays

If an application frequently searches values held inside JSON arrays, the index can be created with:

This option isn’t on by default, so should be enabled intentionally for workloads where array searches are important.

What are the current limitations of JSON indexing in SQL Server 2025?

Unfortunately, as with many new features, there are (currently) some restrictions and limitations to JSON indexing in SQL Server 2025.

First, a table must have a clustered primary key before a JSON index can be created. Furthermore, only one JSON index can currently be created for a particular JSON column, and changing the paths included in an index requires recreating it.

JSON indexes also don’t currently support online index creation or rebuilding, so maintenance operations can require a schema-modification lock on the underlying table.

Finally, there are still some limitations on which predicates can make effective use of the index. For example, the current implementation supports comparisons using JSON_VALUE for JSON-index optimization, but not the LIKE or IS NULL predicates.

Summary: JSON indexing in SQL Server 2025

In SQL Server 2025 JSON indexing becomes a design choice, but you need to decide if you are OK with using a preview feature. Many people have asked for this to be released in a cumulative update (CU) rather than in the next version, but it’s hard to know what will happen.

Either way, computed-column indexes haven’t suddenly become obsolete. They are still useful, particularly when JSON is stored as character data (previously the only option). These indexes can provide very targeted outcomes and let you use other standard features, like included columns.

Overall though, SQL Server 2025’s support for JSON makes it considerably more practical for both developers and DBAs alike.