> ## Content Index
> Fetch the complete content index at: https://devinside.cloud/llms.txt
> Use this file to discover other available public pages before exploring further.

# An index moves work into the write path.
- URL: https://devinside.cloud/indexing-json-read-gains-write-costs-and-maintenance/
- Published: 2026-11-12T15:49:29.000Z
- Updated: 2026-11-13T00:58:15.000Z
- Description: The query asks for documents whose region is two. Without an index on that property, the system may inspect every document. With an appropriate indexed projection, it can navigate to a much smaller candidate set.
- Author: Marcin Borecki
- Tags: Engineering, Data and performance, #eng24, #devinside-review

Historical edition · Sources and examples scoped to 2026-02-05.

The query asks for documents whose region is two. Without an index on that property, the system may inspect every document. With an appropriate indexed projection, it can navigate to a much smaller candidate set.

That is a real design opportunity. It is not the whole cost of the decision.

The projection has to keep meaning the same thing as the JSON document. Updates to the property can move entries between key ranges. Inserts and deletes create maintenance work. Missing properties, conversion failures and inconsistent types can change which records qualify. A fast SELECT does not make those obligations disappear.

Our example stores JSON text in SQL Server 2022 and exposes one property through a computed column and an ordinary B-tree index. It is deliberately not a native JSON index. That distinction prevents newer engine features from quietly entering an older implementation discussion.

Try the maintenance model first. Change the number of documents and regions, then play the read. Switch the index on to see a smaller candidate set. Next change the number of updated documents and run the write phase. The same projection that saves repeated read work now has entries to remove and insert.

The counters are derived from a generated document set and a simplified maintenance rule. They are not page reads, milliseconds or a SQL Server execution. The visual is useful because it refuses to collapse read savings and write obligations into one invented performance score.

## Name the representation you are adding

Suppose an order document has a numeric `region` field. The query boundary should define what a missing region means and which values are valid. If the application expects a small integer, an unconstrained string index is a poor way to express that contract.

```sql
CREATE TABLE dbo.OrderDocuments
(
    Id bigint NOT NULL PRIMARY KEY,
    Document nvarchar(max) NOT NULL,
    Region AS TRY_CONVERT(int, JSON_VALUE(Document, '$.region')),
    CONSTRAINT CK_OrderDocuments_Json CHECK (ISJSON(Document) = 1)
);

CREATE INDEX IX_OrderDocuments_Region
ON dbo.OrderDocuments(Region, Id);

```

This is a design sketch. A production schema should also define the domain constraints for region values and decide whether failed conversion is rejected or represented as unknown. `ISJSON` establishes syntactic JSON validity; it does not prove that the document has the required field or that the field has the domain meaning the application expects.

The computed expression extracts one scalar and converts it to an integer. The index then organizes that projected value. It does not organize every possible path in the document, and it does not magically optimize arbitrary JSON questions.

Microsoft described the computed-column indexing pattern in its [SQL Server 2016 JSON series](https://www.microsoft.com/en-us/sql-server/blog/2016/01/07/json-in-sql-server-2016-part-3-of-4/?ref=devinside.cloud). That historical source predates this edition's context. The example here uses the established text/computed-column pattern rather than importing a later native index implementation.

![An index organizes a particular interpretation](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/projection.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/projection.svg)Swipe the drawing to inspect it.

## Make the query and projection agree

A query can refer directly to the computed column:

```sql
SELECT Id
FROM dbo.OrderDocuments
WHERE Region = @region;

```

This makes the intended typed boundary visible. If another query repeats a JSON expression with a different conversion, path, collation or comparison rule, it may not mean the same thing. An apparently redundant expression can therefore be a semantic difference, not merely an optimizer opportunity.

An application should decide whether the region is numeric data or text that happens to contain digits. If `2`, `"2"` and `"02"` are accepted as the same domain value, that equivalence belongs in the contract and tests. If they are not equivalent, a convenient conversion may erase a distinction the business needed.

The visual uses generated integer regions to keep the access comparison unambiguous. It does not simulate JSON parsing, collation or conversion failure. The article's schema discussion adds those obligations back precisely because a neat animation of sorted keys can otherwise make them easy to forget.

## The index turns repeated discovery into maintained knowledge

Without the selected projection index, the model reads the region of every generated document for every query. With the index, it first selects the bucket for the requested region. Both paths return the same identifiers because they operate on the same domain value.

For 120 documents spread equally over twelve regions, the model inspects 120 candidates without the index and ten candidates with it. That is a count of inspected generated records. It is not a claim of twelvefold elapsed-time improvement. A real query also pays for navigation, page access, storage layout and other operators.

![Repeated discovery versus maintained order](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/read-path.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/read-path.svg)Swipe the drawing to inspect it.

This is the conceptual trade: instead of discovering the region by evaluating the document each time, we maintain an ordered representation that can answer that question more directly. The maintenance cost belongs to writes and storage, even if the read benchmark does not include it.

A covering index can further reduce the need to retrieve columns from another structure. It can also increase index width. Selecting one identifier is not the same workload as returning a whole large document. The projection, key and included columns should follow the query family the product actually needs.

## Updating an unrelated field is not the same as moving the key

Change a document's description while leaving its region unchanged. Now change its region from two to three. Both are document updates, but the second changes where the document belongs in the region ordering.

Our simplified model counts one key removal and one key insertion for each changed indexed value. This is an explanatory maintenance unit, not a prediction of physical page operations, log bytes or SQL Server's exact update plan. It makes the additional representation visible without claiming to reproduce the engine.

If the key remains unchanged, the model records no movement between region buckets. That does not establish zero write overhead in a real system. The engine may still evaluate expressions, update other index values, log changes or perform other work. “No modeled key relocation” is the precise statement.

![Changing a key changes its home](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/write-movement.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/write-movement.svg)Swipe the drawing to inspect it.

The useful measurement later is therefore not just “UPDATE took longer.” Record what changed: the indexed property, an unrelated property, the document size, the number of affected rows and the indexes present. Different update shapes answer different questions.

## Missing, invalid and null are not interchangeable by default

JSON syntax can be valid while a region field is absent. The field can contain explicit null, a nonnumeric string, a number outside the accepted range or a structure where a scalar was expected. A typed projection has to decide which of those become null, which are rejected and which are accepted.

A tolerant conversion is useful when the application deliberately represents invalid or unavailable input as unknown. It is dangerous when it silently moves malformed data out of an equality query and the application interprets absence as proof that no such record exists.

Write the expected set for each edge case independently. A test can insert a small fixed corpus covering the permitted shapes and compare the selected identifiers with a manually defined oracle. Do not ask the computed column itself to generate the expected answer; that would repeat the same interpretation on both sides of the assertion.

The index also makes the contract durable across future writers. If a new ingestion path stores strings where an older path stored numbers, the query can remain syntactically valid while its meaning changes. A schema boundary should catch that drift before a performance dashboard turns it into a mysterious drop in results.

## Read frequency changes the decision, but not the units

An index used by a frequent selective query may justify substantial maintenance. The same index may be a poor choice for a write-heavy stream whose rare queries usually select most documents. The answer depends on the workload, not a universal preference for indexed JSON.

The model includes a query-frequency control. It multiplies candidate inspections across repeated reads. Write maintenance is displayed separately, in key-entry operations. They intentionally remain separate counters because an inspected row and a key relocation do not have an established common cost.

![Do not invent a shared cost unit](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/workload-plane.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG24/workload-plane.svg)Swipe the drawing to inspect it.

You can supply measured unit costs in a later study, but those costs would be conditional on the engine, data, hardware and concurrency. A synthetic weighted sum with arbitrary coefficients can look analytical while merely encoding the author's preferred answer. Keeping the axes separate is more honest until the experiment establishes how to combine them.

## Inspect the executed plan without granting it more meaning than it has

For an actual comparison, capture the plan and I/O evidence for both query shapes under the same data and parameters. Confirm the returned identifiers first. Then inspect which structure the optimizer selected and how many rows it encountered and returned.

```sql
SET STATISTICS IO ON;
SET STATISTICS XML ON;

SELECT Id
FROM dbo.OrderDocuments
WHERE Region = @region;

SET STATISTICS XML OFF;
SET STATISTICS IO OFF;

```

This is a reproducible inspection recipe, not historical execution evidence. A plan showing a seek does not by itself establish an efficient whole operation. It can still inspect a broad range, perform lookups, or sit inside a larger query whose expensive work happens elsewhere.

Keep estimates and observations separate. If the estimated cardinality is far from the observed result, investigate why the optimizer's model differs from the data. If the estimates are accurate but the query is still expensive, the design may simply require a large amount of work.

## Storage and maintenance need their own experiment

The index occupies space and changes write behavior. A serious decision records those consequences alongside read improvements. It should also identify costs omitted from the timing boundary, such as data preparation, index creation, cache warming or maintenance between measurement rounds.

Comparing two tables can make the experimental treatment clear: one has the projection index and one does not. It also creates a duty to verify that their logical contents remain equivalent. Comparing successive configurations of one table avoids duplicate data but introduces state and cache-order concerns. Neither design makes interpretation automatic.

Use enough observations to understand variation, but do not hide an inconvenient run merely because it weakens the preferred story. A small local experiment can justify a focused next step. It cannot establish production throughput or a universal speedup across all document shapes.

The original current laboratory preserves actual plans, measurements and exact versions separately. This historical edition supplies the mechanism and experimental questions without presenting those later observations as already known.

## Decide what would make you remove it

An index proposal is more useful when it includes a reversal condition. Perhaps the query becomes rare, the dominant predicate changes, the write stream grows, or a different representation replaces the JSON property. Without that condition, indexes accumulate as monuments to past bottlenecks.

I would record the served query family, expected selectivity, update shapes, storage cost and correctness assumptions. Then I would monitor whether the workload still matches that argument. A performance feature should retain an owner just as a background task does.

The right question is not “can JSON be indexed?” The established computed-column approach already answers that. The useful engineering question is whether this maintained representation makes the application's important work cheaper without hiding a more expensive or less reliable path somewhere else.

GO FURTHER

## Inspect the contemporary laboratory.

This is a newly reconstructed historical edition, not a claim of earlier authorship. Later executed experiments, their exact environments and original results are preserved in a separate notebook.

[Open the current laboratory notebook ↗](https://devinside.cloud/lab-notebooks/v4/ENG24.html)

[Download the current reconstruction sources ↗](https://devinside.cloud/content/files/v4/ENG24-reconstruction.zip)