Before SQL Server 2025, if you want to store JSON data in Microsoft SQL Server or Azure SQL DB, and you want fast queries, the easiest way is to:

• Store the data in an NVARCHAR(MAX) column (because the native JSON datatype didn’t arrive until SQL Server 2025)
• Add a computed column for the specific JSON keys we’ll want to query quickly
• Index those keys
• Query it using the JSON_VALUE function

To demo what I mean, I’m going to take the Users table from the Stack Overflow database, and I’m going to pretend that we’re only storing the Id column, and the rest of the columns are JSON, stored in a UserAttributes column. Let’s create a new Users_JSON table to simulate it:

Now we’ve got a table with just Ids and JSON data:

If we want to find the users with DisplayName = ‘Brent Ozar’, and we query with the JSON_VALUES function, SQL Server has to scan through that entire table, cracking open every JSON value, and finding it. That’s a lot of reading, and a lot of CPU work:

The query is slow and CPU-intensive, going parallel across multiple cores, maxing our server out for about 4 seconds:

This stuff works in the sense that it compiles and runs, but as you hit real-world data sizes (there are less than 10 million rows in that table), that doesn’t scale. We’re gonna need to run more than one query every 4 seconds.

Add a computed column and index it.

We do have to decide what columns we wanna index it, but take a deep breath and relax – I’m not saying you need to do any application code work at all. We’re just going to make some of the columns really fast. Take DisplayName:

Now, run the exact same JSON_VALUE query without changing our app or our code:

The query runs instantly, reading hardly any data and doing no CPU work:

SQL Server automatically recognizes what we’re trying to do in the query, realizes it’s got an indexed computed column ready to go, and uses that to deliver our query results. It’s like magic, and it’s worked this way since 2016 with JSON_VALUE. Works with other datatypes too, like numbers and dates.

It even works with LIKE queries:

Producing a nice index seek, and even pretty good row estimations. Here, SQL Server estimates 3699 rows will be produced (and 1523 are):

If we do a non-sargable filter, like a leading % sign in our LIKE:

SQL Server still uses the index, just scanning it instead of seeking it, which is a good thing:

Scan SOUNDS bad, but it’s still less CPU work and logical reads than our un-indexed JSON scan. Back at the beginning of the post, we were looking at half a million reads and 14 seconds of CPU work to scan all the JSON, but with the index, even non-sargable stuff isn’t terrible:

Once you’ve got the computed column and index in place, you could change your queries to be normal T-SQL, like this:

They’ll be super-fast (just like the JSON_VALUE queries) – but I don’t recommend doing this. If you change your queries to point to the computed column, then I always have to have the computed column in place in the database. Leaving your queries as JSON_VALUE queries means we keep the flexibility of changing the database, plus we get fast index seeks. There’s no benefit to changing our queries to point to the new computed column – only heartache when our schema needs eventually change.

This indexed-computed-column technique has worked really well.

It’s a nice compromise that works well when the developers want the flexibility of changing which attributes they store, adding more attributes over time – but some attributes need really fast searches. I’ve been teaching it for years in this module of my Mastering Index Tuning class, where I also talk about the drawbacks and other solutions. (You can join in on these classes for a special price during my Black Friday sale this month!)

Developers are happy because they can change their UsersAttributes schema whenever they want without talking to the DBA. They can even decide to remove columns that used to be part of our core fast-query design – as long as they tell the DBA, and the DBA drops the index and computed column. The developers don’t have to change their queries – they just keep using JSON_VALUE. The results are fast when we agree that it’s one of the core columns, and still doable when they’re outside the core set of columns, just slower.

One drawback is that the NVARCHAR(MAX) datatype isn’t really JSON: SQL Server doesn’t validate the data for you. Another is that if you want multiple filters, you have to write them out individually. Another is that the more complex your JSON becomes, the more you have to pay attention to stuff like lax mode and strict mode. I’mma be honest, dear reader: like the lion, I don’t concern myself with such things, and I just point the developers to the documentation. I say look, if you want JSON queries to be millisecond-fast in SQL Server and Azure SQL DB, you gotta tell me the specific columns you’re going to query, in advance, and I’m gonna tell you how to write the query.

But the biggest drawback – by far – is that we have to define the core set of columns we wanna query quickly.

Developers want real flexibility: the ability to change their JSON schema at any time, and query any values out of it, quickly, without friction from the database side. That’s what Microsoft tried to deliver in SQL Server 2025, and I’ll cover that in the next post in the series. Careful what you ask for, though….