One SQL Cheat Code For Amazingly Fast JSON Queries - SQL JSON Index performance

How non-persisted computed column indexes make your JSON queries high performance.

Did you know that non-persisted columns actually get saved to the index pages? This makes JSON querying incredibly fast in SQL Server.

Please like and subscribe if you enjoyed this video!

Blog post:

Elsewhere on the internet:

[deeg]

What's the difference between a persisted computed column, and an indexed non-persisted computed column then? Isn't an index being stored "on disk"?

[krazee_aznnn]

Hey Bert, nice meeting you at SQL Saturday in Rochester recently.
I'm trying to apply this technique to a new proof of concept project I'm working on, and I had a question (not sure if you'll know the answer or have a solution, but I figured I'd ask anyway).

Long story short, I have JSON data with specific fields and I want to create this non-persisted computed column to index, but I don't want the index value to appear for all rows.

Example: I have multiple rows in which they have an "Id" field in the JSON, but the rows differ with different structured JSON (let's say structure A and structure B). I want to index JSON structure A on the Id, but only if it matches this structure (and exclude, or make the value in this index null for non-structure A JSON even though structure B JSON has an Id field).
Can you think of any way to accomplish this?
Or am I going about this in a way too difficult mechanism?

[chobo]

Hey

How would you handle with JSON if you wanted to normalize your data. Say for instance you have a pre-defined list of options

lets take your car example, So you have Fuel: which could have Diesel or Gas). Now I probably would end up making some sort of table to hold these 2 choices. Now maybe I sell an array of different products that may not have fuel spect(maybe it is hand powered or it's a brick) so I would stick this in my json column. What happens if I want to store the id instead of the label name (ie Fuel: 1 vs Fuel : Gas")

How would I later on doing a join so I can get the proper label name later on?

[stefanotorelli]

My job mates do not even knows how and when to use indexes on json columns.... that is so bad... ORM frameworks deserializing json to look for specific properties.. so bad... that's so bad..