XML与JSON较量：在SQL Server中应使用哪个？

Emanuele Rosso的 “ 决斗即决斗 ”获得CC BY-NC-ND 2.0的许可

在 更新的帖子中 提供了其他性能比较 。

快来看我 在7月22日的SQL Saturday Columbus上 谈论JSON和XML的事情 ！

从2016版本开始，SQL Server提供本机JSON支持。 尽管实现并不完美， 我 是 还是 一个 巨大的 风扇 。

即使像JSON支持这样的新功能很棒，但只有在实用且性能优于替代功能的情况下，我才可能使用它。

今天，我想将JSON与XML进行比较，看看哪种是在SQL Server中使用的更好的格式。

输入XML，SQL的坏习惯

全面披露：我不喜欢XML，也不喜欢SQL Server对其的实现。

XML过于罗word（大量字符浪费在结束标记上），它具有元素AND属性（我不喜欢不得不针对两种不同的情况进行编程 ），并且取决于您所用的编程语言，有时您需要模式文件和有时你没有。

SQL Server的XML实现确实具有一些不错的功能，例如专用数据类型可以减少存储空间并验证语法，但是我发现对XML的查询很笨拙。

除了所有的XML问题，如果XML胜过JSON，我仍然愿意使用XML。 因此，让我们运行一些测试查询！

JSON SQL Server是镇上的新警长吗？

尽管性能是这些比较测试的最终决定因素，但我认为JSON仅在可用性方面比XML领先。 SQL Server的JSON函数签名更容易记住，也更容易在屏幕上编写。

我正在使用的测试数据是https://github.com/arthurkao/vehicle-make-model-data的车辆年份/品牌/型号数据。 这是我将其加载到名为dbo.XmlVsJson的表中后的dbo.XmlVsJson ：

CREATE TABLE dbo.XmlVsJson
(
  Id INT IDENTITY PRIMARY KEY,
  XmlData XML,
  JsonData NVARCHAR(MAX)
)

（如果您想在家玩游戏， 可以在此要点中找到完整的数据查询 ）

资料大小

所以XML应该更大一些吧？ 它具有所有这些重复的结束标记？

SELECT
  DATALENGTH(XmlData)/1024.0/1024.0 AS XmlMB,
  DATALENGTH(JsonData)/1024.0/1024.0 AS JsonMB
FROM
  dbo.XmlVsJson

原来XML实际上更小！ 怎么会这样？ 这就是SQL Server XML数据类型背后的魔力。 SQL不会将XML存储为巨大的字符串。 它仅存储XML InfoSet ，从而减少了空间。

另一方面，JSON存储为常规的旧nvarchar（max），因此其完整字符串内容将写入磁盘。 在这种情况下，XML胜出。

插入性能

因此，在使用XML数据类型时，与在nvarchar（max）数据类型中使用JSON相比，XML在物理上存储的数据更少，这是否意味着它也会更快地插入？ 这是我们的查询，它尝试从第一个查询中插入100行重复记录：

SET STATISTICS TIME ON

INSERT INTO dbo.XmlVsJson (XmlData)
SELECT XmlData FROM dbo.XmlVsJson 
  CROSS APPLY 
  (
    SELECT DISTINCT number 
    FROM master..spt_values 
    WHERE number BETWEEN 1 AND 100
  )t WHERE Id = 1
GO

INSERT INTO dbo.XmlVsJson (JsonData)
SELECT JsonData FROM dbo.XmlVsJson 
  CROSS APPLY 
  (
    SELECT DISTINCT number 
    FROM master..spt_values 
    WHERE number BETWEEN 1 AND 100
  )t WHERE Id = 1
GO

结果呢？ 在我的机器上插入100个XML行需要613毫秒，而插入100个JSON行需要1305毫秒… XML再次获胜！

JSON看起来不太热。 等等...

我在猜测，因为XML数据类型实际上存储的数据较少，所以也可以更快地将其写到表中是有意义的。

CRUD操作

与.NET相比，SQL Server的JSON性能给我留下了难以置信的印象，但是与SQL Server上的XML 相比，它又如何呢？

读

让我们从XML和JSON中选择第二辆车的片段：

SELECT t.XmlData.query('/cars/car[2]') 
FROM dbo.XmlVsJson t 
WHERE Id = 1

SELECT JSON_QUERY(t.JsonData, '$.cars[1]') 
FROM dbo.XmlVsJson t 
WHERE Id = 1

结果？ 当需要从较大的对象字符串中提取一个片段时， JSON胜出 （0ms，XML为63ms）。

如果我们想获取特定值而不是片段怎么办？

SELECT t.XmlData.value('(/cars/car[2]/model)[1]', 'varchar(100)') FROM dbo.XmlVsJson t 
WHERE Id = 1

SELECT JSON_VALUE(t.JsonData, '$.cars[1].model') 
FROM dbo.XmlVsJson t 
WHERE Id = 1

JSON再一次赢得了0毫秒，而XML 赢得了11毫秒。

如果查看最后两个查询的执行计划，很容易看到XML在后台检索数据方面还有很多工作要做：

XML：

JSON：

创建

上面我们看到，插入XML数据行比插入JSON行要快，但是如果我们想将新数据插入对象字符串本身呢？ 在这里，我想将属性“ mileage”插入第一个汽车对象：

UPDATE t SET XmlData.modify('
insert <mileage>100,000</mileage>
into (/cars/car[1])[1]') 
FROM dbo.XmlVsJson t 
WHERE Id = 1

UPDATE t SET JsonData = JSON_MODIFY(JsonData,
'$.cars[0].mileage','100,000') 
FROM dbo.XmlVsJson t 
WHERE Id = 1

除了更JSON_MODIFY()语法（ JSON_MODIFY()与REPLACE()基本上相同）之外，JSON插入的运行时间为22ms，而XML的运行时间为206ms。 另一个JSON胜利。

更新资料

让我们更新刚刚添加的里程属性，使其值为110,000：

UPDATE t SET XmlData.modify('
replace value of (/cars/car[1]/mileage/text())[1]
with     "110,000"') 
FROM dbo.XmlVsJson t
WHERE Id = 1

UPDATE t SET JsonData = JSON_MODIFY(JsonData, '$.cars[0].mileage','110,000') 
FROM dbo.XmlVsJson t
WHERE Id = 1

结果？ JSON具有更快的绘制速度，并且能够在54毫秒内完成此更新，而XML则是194毫秒。

删除

删除大型字符串数据是DBA的梦想。

让我们删除Mileage属性，撤消刚才所做的所有辛苦工作：

UPDATE t SET XmlData.modify('
delete /cars/car[1]/mileage[1]') 
FROM dbo.XmlVsJson t 
WHERE Id = 1

UPDATE t SET JsonData = JSON_MODIFY(JsonData, '$.cars[0].mileage', null) 
FROM dbo.XmlVsJson t 
WHERE Id = 1

JSON不需要花费任何时间来重新加载，并且在 50ms至159ms的时间内再次赢得了XML的支持。

阅读第二部分：索引

因此，在上面我们看到，从单行序列化数据中读取片段和属性，JSON比XML快。 但是我们的SQL Server可能有很多行的数据-索引数据解析在我们的匹配中做得如何？

首先，让我们扩展数据—而不是将所有汽车对象存储在一个字段中，让我们建立一个新表，将每辆汽车都放在自己的行中：

（再一次，如果您在家中，请在GitHub上获取完整的数据集 ）

现在我们在表中有了扩展的数据，让我们添加一些索引。 SQL Server中的XML数据类型具有自己的索引类型，而JSON仅需要一个已应用常规索引的计算列 。

DROP INDEX IF EXISTS PXML_XmlData ON XmlVsJson2
CREATE PRIMARY XML INDEX PXML_XmlData
ON XmlVsJson2 (XmlData);

ALTER TABLE dbo.XmlVsJson2
ADD MakeComputed AS JSON_VALUE(JsonData, '$.make')
CREATE NONCLUSTERED INDEX IX_JsonData ON dbo.XmlVsJson2 (MakeComputed)

（注意：我还尝试添加 XML二级索引 以获得更好的性能，但是我无法让查询引擎在这样的基本数据集中使用该二级索引）

如果我们尝试查找所有与谓词匹配的行：

SELECT Id, XmlData 
FROM dbo.XmlVsJson2 t 
WHERE t.XmlData.exist('/car/make[.="ACURA"]') = 1

SELECT Id, JsonData 
FROM dbo.XmlVsJson2 t 
WHERE JSON_VALUE(t.JsonData, '$.make') = 'ACURA'

XML能够在200毫秒内过滤掉96行，而JSON在9毫秒内完成过滤。 JSON的最终胜利 。

结论

如果您需要在SQL Server中存储和处理序列化的字符串数据，那么毫无疑问：JSON是首选格式。 尽管JSON的存储大小比XML的前辈大一点，但SQL Server的JSON功能在几乎所有情况下的速度都优于XML。

是否有足够的性能差异将所有旧的XML代码重写为JSON？ 可能不是，但每种情况都不同。

一件事很清楚：新开发应考虑利用SQL Server的新JSON函数。

喜欢这篇文章吗？ 请通过在 下面 给它一个绿色的心 recommend来推荐它 。

您是否仍计划在SQL Server 2016+中使用XML而不是JSON进行新开发？ 我很想知道为什么在下面的评论中。

From: https://hackernoon.com/xml-vs-json-shootout-which-should-i-use-in-sql-server-7eefa4dc7553