JSON vs XML Indexing in SQL Server - The Ultimate Performance Showdown! (Benchmark Inside)

JSON vs XML Indexing in SQL Server – The Ultimate Performance Showdown (Real Benchmarks Inside)

SQL Server Performance Series – Advanced Indexing Deep Dive

Related article in this series: SQL Server Performance Series – Engine-Level Optimization Deep Dive

Hi SQL SERVER guys, today we will answer to a question that keeps coming back in modern SQL Server workloads:

Should you index XML… or JSON?

Which one performs better? Which one scales? Which one burns your CPU?

As you usually do let’s benchmark it properly.

---

Why This Matters

Modern applications store semi-structured data everywhere:

• Microservices payloads
• Audit logs
• Dynamic attributes
• External API responses

SQL Server supports both XML and JSON standards, but indexing strategies are completely different.

---

How XML Indexing Works

XML supports the following types of indexes:

• Primary XML Index
• Secondary PATH index
• Secondary VALUE index
• Secondary PROPERTY index

It shreds XML into internal relational structures. This approach is Powerful but also heavy.

---

How JSON Indexing Works

JSON doesn't have a native JSON index type.

Instead we use computed columns plus traditional indexes.

ALTER TABLE Orders
ADD CustomerID_JSON AS JSON_VALUE(OrderJson, '$.CustomerID');

CREATE INDEX IX_Orders_CustomerID_JSON
ON Orders(CustomerID_JSON);

This approach makes JSON indexing lighter and more flexible. Keep it in mind.

---

Benchmark Setup

Test scenario:

• 1,000,000 rows
• Same logical structure stored once as XML and once as JSON
• Query filtering by CustomerID

Hardware: A standard lab VM.
(No tricks. No magic)

---

Test Query – XML

SELECT *
FROM Orders_XML
WHERE OrderData.value('(/Order/CustomerID)[1]', 'INT') = 5000;

My results

• No XML Index → 4200 ms, high CPU
• Primary + PATH Index → 380 ms
• Storage increase: +65%

---

Test Query – JSON

SELECT *
FROM Orders_JSON
WHERE CustomerID_JSON = 5000;

My results

• No index → 3900 ms
• Computed column + index → 120 ms
• Storage increase: +8%

---

CPU Comparison

• XML Indexed: moderate CPU, complex execution plan
• JSON Indexed: lower CPU, clean index seek

Here then JSON indexing wins in raw speed and storage efficiency.

---

When XML Still Wins

• Complex hierarchical queries
• XQuery heavy workloads
• Strict schema validation needs

XML is more powerful in expressive querying. But it costs more.

---

A look to the Execution Plan

XML indexing introduces:

• XML Index Seek operators
• Internal node table access
• Higher memory usage

JSON indexing behaves like:

• Classic nonclustered index seek
• Simple predicate evaluation
• Lower memory grant

---

Storage Impact Comparison

• XML Primary Index can double table size (Keep it in mind!!!)
• JSON computed column index adds minimal overhead

On very large systems, this difference becomes massive.

---

The Final Verdict (...and the winner is...)

For performance + scalability → JSON indexing wins.

For complex document querying → XML is more expressive.

If you care about raw speed and storage efficiency, JSON with computed column indexing is usually the smarter choice.

---

The Real Lesson

Performance tuning is not about features.

It’s about trade-offs.

JSON is lighter. XML is more powerful.

Choose based on workload — not trend.

---

And remember…

No SQL Server was harmed during these benchmarks

See you in the next deep dive.

Luca Biondi @2026