The filter looks simple, but extracting a property from every document still creates work. Indexing JSON properties through computed columns gives SQL Server an ordinary indexed value to search.
Expose the Property Your Query Needs
A JSON document can hold flexible attributes while a query repeatedly searches one stable property. A computed column exposes that property as a relational expression. An index on the expression supplies a searchable structure. The database still retains the original document, so the design does not require duplicating application maintenance logic.
I review the frequent predicates before selecting computed properties. Indexing every possible attribute adds write cost and storage without a clear workload benefit. Start with a stable path used by an important query. Check the intended result type and acceptable missing-value behavior before choosing the expression.
The following example uses a scratch table in a test database. Its inputs create documents with customer identifiers and colors. GENERATE_SERIES requires SQL Server 2022 or later with compatibility level 160 or higher. The document functions work in recent supported versions. These values are demonstration inputs, rather than observed application data.
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
CREATE TABLE dbo.JsonOrders
(
OrderID int NOT NULL PRIMARY KEY,
Payload nvarchar(max) NOT NULL CHECK (ISJSON(Payload) = 1)
);
INSERT dbo.JsonOrders
SELECT value, CONCAT(N'{"customerId":', value % 500,
N',"color":"blue"}')
FROM GENERATE_SERIES(1, 10000);Observe the Predicate Before Indexing JSON
Enable an actual execution plan in SSMS and run the original predicate. STATISTICS IO reports the logical reads used by this statement. Inspect where document extraction appears and which access path supplies candidate rows. A table scan is understandable when no selective indexed expression supports that property search.
SET STATISTICS IO ON;
SELECT OrderID
FROM dbo.JsonOrders
WHERE TRY_CONVERT(int, JSON_VALUE(Payload, '$.customerId')) = 12;
SET STATISTICS IO OFF;Do not promise that every document predicate must scan under every schema. Other indexed predicates can narrow the candidates first. SQL Server 2025 also offers additional native JSON indexing capabilities. This example specifically demonstrates the computed-column route. Judge that route using the query and indexes actually present.
Choose a Bounded Useful Type
JSON_VALUE's usual textual result is nvarchar(4000). Indexing that full potential width risks exceeding a nonclustered index key limit. Cast a numeric business identifier to int when that is its actual contract. Choose a bounded string type only when its length and collation match accepted values.
ALTER TABLE dbo.JsonOrders
ADD CustomerID AS TRY_CONVERT(int, JSON_VALUE(Payload, '$.customerId'));
CREATE INDEX IX_JsonOrders_CustomerID
ON dbo.JsonOrders(CustomerID);
SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.JsonOrders'),
N'CustomerID', 'IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY(OBJECT_ID(N'dbo.JsonOrders'),
N'CustomerID', 'IsPrecise') AS IsPrecise;TRY_CONVERT turns missing or invalid integer representations into NULL. That keeps this extraction tolerant, but it requires a separate validation policy. Do not accept malformed required identifiers simply because the computed value is indexable. Retain an exception query that distinguishes absent properties from wrong types or out-of-range values.
Match the Expression and Path
Use the computed column directly for the clearest contract. SQL Server can also match an equivalent expression to an indexed computed column. The extraction path, conversion, and relevant semantics must agree. Similar-looking paths are not interchangeable. Property names use JSON's case-sensitive matching rules.
SET STATISTICS IO ON;
SELECT OrderID FROM dbo.JsonOrders WHERE CustomerID = 12;
SELECT OrderID FROM dbo.JsonOrders
WHERE TRY_CONVERT(int, JSON_VALUE(Payload, '$.customerId')) = 12;
SET STATISTICS IO OFF;Compare both actual plans. Look for a seek on the new index and confirm that returned identifiers agree. A seek is an available choice, rather than a command enforced by the computed column. If the predicate selects most rows, a scan can remain the cheaper option. That result needs interpretation rather than alarm.
Decide Whether Persistence Earns Its Space
A computed column does not need PERSISTED merely because you want an index. When the expression meets indexing requirements, the index stores the calculated key values. The nonpersisted expression does not create an extra stored value in the base row. Its maintenance still participates in relevant writes.
PERSISTED stores the computed value in the table as well. That adds another storage and maintenance consideration. Use it when a documented requirement or measured workload benefit justifies it. Do not assume it automatically improves every indexed-property query. The index already provides values needed for its own access path.
I compare read benefits with document-update cost before expanding this pattern. Updating the source payload can require computed-value and index maintenance. Include insert, update, and delete operations in the test. A read-only benchmark hides the cost paid by the application's write path.
Keep Session Options Consistent for Indexing JSON
The opening block sets the required options explicitly. ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER must be ON. NUMERIC_ROUNDABORT must be OFF. The creation connection and modifying connections need the required settings, and SELECT sessions need compatible settings for index use.
Do not certify the design using only your SSMS connection. Application sessions can use different options. Test through the actual application identity and connection configuration. Inspect errors from writes and plans from reads. A correctly created index still needs correctly configured sessions to deliver the intended behavior.
Indexability also depends on expression determinism, precision, ownership, and supported types. Check the relevant computed-column requirements when extending this example. A convenient function added to the expression can change those properties. Keep the definition simple enough that reviewers can explain every conversion.
Cover the Query Without Covering Everything
The nonclustered index includes the clustered primary key needed to identify a row in this example. Queries selecting additional ordinary columns can use INCLUDE where the workload supports it. Returning the whole large payload still requires access to that content. Review lookup cost before declaring the property index complete.
Wide included values make the index more expensive to store and maintain. Choose the actual output columns from representative statements. Several almost-identical property indexes deserve a consolidation review. An index collection can become a second document archive if every query receives its own generous payload copy.
Which property must remain searchable after the document format changes? Keep its path and type in the versioned document contract. Rehearse new formats, missing properties, invalid numeric values, and long strings. Validate both old and new application queries while a rollout supports more than one accepted document version.
Measure Indexing JSON as a Complete Access Path
Indexing JSON helps when a stable property supports selective searches. Compare actual plans, logical reads, write costs, and accepted results on representative data. Keep the unindexed statement as a baseline and retain the exact indexed expression with the test evidence.
Indexing JSON should serve a defined predicate rather than a vague promise about document speed. Keep the type, path, session options, and maintenance cost visible. Those details turn a flexible document property into a dependable relational access path.
Related reading on this blog: Indexed Computed Columns: Speeding Up Code You Cannot Change and SQL SERVER Performance: JSON vs XML.
A computed-property index is not a shortcut around data rules, it is a searchable expression with its own contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
