👀 Views: 61 💬 Answers: 1 📅 Created: 2025-06-06

sql-server json performance SQL

This might be a silly question, but I am trying to optimize the performance of my SQL Server 2019 application that heavily relies on JSON data. I have a table called `UserData` that contains a `UserInfo` column of type `NVARCHAR(MAX)` where I store JSON strings. The question arises when I attempt to query nested JSON properties and return results quickly. For example, I am trying to extract a user's email from the JSON structure using the following query: ```sql SELECT JSON_VALUE(UserInfo, '$.email') AS Email FROM UserData WHERE JSON_VALUE(UserInfo, '$.status') = 'active'; ``` While this works, I notice that performance is significantly degraded when the table has around 1 million rows. I decided to create a computed column and an index to improve query performance: ```sql ALTER TABLE UserData ADD Email AS JSON_VALUE(UserInfo, '$.email'); CREATE INDEX IX_UserEmail ON UserData(Email); ``` However, when I run my SELECT query, it still takes a long time to execute. I checked the execution plan, and it shows that SQL Server is performing a table scan instead of using the index. I also tried updating statistics but that didn't help. I read that SQL Server might not optimize for computed columns that depend on JSON functions. Is there a way to make this approach work efficiently, or should I consider restructuring my database? Additionally, would using a `STRING_AGG` or different data type for storage yield better performance? I'm looking for solutions or best practices that others have implemented in similar scenarios. Any insight would be appreciated! For context: I'm using Sql on Linux.