JSON Column Index in SQL Server: Index One Property

A JSON column index is one of the first tools a DBA needs when documents arrive in a relational server. I wrote about the changing DBA role in April 2018. My consulting conversations with data professionals and business leaders shaped that article.

By 2018, I was seeing DBAs work more closely with development and DevOps teams. Many also supported nonrelational platforms or moved workloads to cloud services. NoSQL covers document, key and value, and graph stores, so it is not one product. SQL Server can hold a document too, in a column. That makes it a safe place to practice. I tested everything below on SQL Server 2025.

A Document Lives in One Column

My test table stores 100,000 recipes as JSON text. A check constraint with ISJSON keeps broken documents out. A document with a missing quote was rejected with error 547.

CREATE TABLE dbo.Recipes (
    RecipeId int IDENTITY(1,1) PRIMARY KEY,
    Doc nvarchar(max) NOT NULL CONSTRAINT CK_Recipes_Doc CHECK (ISJSON(Doc) = 1));
INSERT dbo.Recipes (Doc)
SELECT CONCAT(N'{"name":"Recipe ', value, N'","cuisine":"',
       CHOOSE(value % 5 + 1, N'Gujarati', N'Punjabi', N'Tamil', N'Bengali', N'Maharashtrian'),
       N'","minutes":', 10 + value % 50, N',"tags":["vegetarian","home"],"notes":"',
       REPLICATE(N'x', 120), N'"}')
FROM GENERATE_SERIES(1, 100000);

BEGIN TRY
  INSERT dbo.Recipes (Doc) VALUES (N'{"name": "Broken');
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS err, ERROR_MESSAGE() AS msg;
END CATCH

Search Inside the Document

JSON_VALUE reads one property as text. Asking for every Tamil recipe means opening all 100,000 documents. The query read 5,873 pages and found 20,000 rows. The table itself holds about 5,850.

SET STATISTICS IO ON;
SELECT COUNT(*) AS n FROM dbo.Recipes WHERE JSON_VALUE(Doc, '$.cuisine') = N'Tamil';

Index the Property with a Computed Column

SQL Server can’t index inside a text column. It can index a computed column that exposes one property. My first try wrapped the value in a CAST to a short type. Filtering on the new column read 64 pages. The original JSON_VALUE query still read 5,873, because the cast changed the expression and the optimizer didn’t match it.

ALTER TABLE dbo.Recipes ADD CuisineCast AS CAST(JSON_VALUE(Doc, '$.cuisine') AS nvarchar(30));
GO
CREATE INDEX IX_Recipes_CuisineCast ON dbo.Recipes (CuisineCast);
GO
SELECT COUNT(*) AS n FROM dbo.Recipes WHERE JSON_VALUE(Doc, '$.cuisine') = N'Tamil';
SELECT COUNT(*) AS n FROM dbo.Recipes WHERE CuisineCast = N'Tamil';

The second try defined the column as the bare JSON_VALUE expression. Now both forms of the query read 63 pages. Existing queries benefit without a rewrite. SQL Server warned that the key could reach 8,000 bytes against a 1,700 byte limit. An insert fails only if a value is that long, and mine are short.

DROP INDEX IX_Recipes_CuisineCast ON dbo.Recipes;
ALTER TABLE dbo.Recipes DROP COLUMN CuisineCast;
GO
ALTER TABLE dbo.Recipes ADD Cuisine AS JSON_VALUE(Doc, '$.cuisine');
GO
CREATE INDEX IX_Recipes_Cuisine ON dbo.Recipes (Cuisine);
GO
SELECT COUNT(*) AS n FROM dbo.Recipes WHERE JSON_VALUE(Doc, '$.cuisine') = N'Tamil';
SELECT COUNT(*) AS n FROM dbo.Recipes WHERE Cuisine = N'Tamil';

Use a Number Type for Numbers

JSON_VALUE returns text, so the minutes in a recipe come back as strings. A computed column with CAST ... AS int compares as numbers, and its index stays small. Here is a range search on cooking time, with the size of each index.

ALTER TABLE dbo.Recipes ADD Minutes AS CAST(JSON_VALUE(Doc, '$.minutes') AS int);
GO
CREATE INDEX IX_Recipes_Minutes ON dbo.Recipes (Minutes);
GO
SELECT COUNT(*) AS n FROM dbo.Recipes WHERE Minutes BETWEEN 10 AND 12;
SELECT i.name, ps.in_row_data_page_count AS pages
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('dbo.Recipes') AND i.index_id > 1;

The range search found 6,000 recipes with 13 reads. The integer index took 174 pages. The text index on cuisine took 372. A narrow numeric type keeps an index small.

Missing Keys Return NULL

Documents in one column don’t all carry the same keys. I added ten recipes with no cuisine. Counting the keys across all documents shows the gap. The OPENJSON query lists each key and how many documents carry it.

INSERT dbo.Recipes (Doc) SELECT CONCAT(N'{"name":"Odd ', value, N'","minutes":5}') FROM GENERATE_SERIES(1, 10);
SELECT COUNT(*) AS without_cuisine FROM dbo.Recipes WHERE JSON_VALUE(Doc, '$.cuisine') IS NULL;
SELECT k.[key] AS property, COUNT(*) AS documents
FROM dbo.Recipes AS r
CROSS APPLY OPENJSON(r.Doc) AS k
GROUP BY k.[key]
ORDER BY documents DESC, property;

The count was 10, found in 3 reads through the new index. The key list shows cuisine, notes and tags on 100,000 documents and name and minutes on 100,010.

By default, a missing property returns NULL. Adding strict to the path raises error 13608 instead. I use strict mode in checks where a missing key means bad data.

DECLARE @Doc nvarchar(max) = N'{"name":"Recipe 1","minutes":20}';
DECLARE @Strict nvarchar(50) = N'strict $.cuisine';
SELECT JSON_VALUE(@Doc, '$.cuisine') AS missing_in_lax_mode;
BEGIN TRY
  SELECT JSON_VALUE(@Doc, @Strict) AS missing_in_strict_mode;
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS err, ERROR_MESSAGE() AS msg;
END CATCH

What the DBA Adds

When a team asks to store documents, I ask which properties they filter on. Those get a computed column and an index. The rest stay in the document. Every index still costs writes, so I keep the list short.

A document column also moves the schema into the application. When nobody records which keys exist, the DBA has to find out. A key coverage query like the one above is the first page of that record. I rerun it before any change to the indexed properties. If a new key appears in the data, I ask the team whether anyone will filter on it.

To list every key in a column, see Schema Discovery for JSON Columns. To turn properties into typed columns, see OPENJSON WITH Clause.

To check a database after a move, see Verify a Migrated Database: Row Counts and Checksums per Table.

A document column is not a way around the schema, it is a schema you still have to look after.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.