儲存 JSON 文件

適用於： SQL Server 2016 （13.x）及以後版本 Azure SQL Database Azure SQL Managed Instance Microsoft Fabric 中的 SQL 資料庫

SQL 資料庫引擎提供原生 JSON 函數，可讓您使用標準 SQL 語言剖析 JSON 文件。您可以將 JSON 檔儲存在 SQL Database 引擎中，並將 JSON 數據查詢為 NoSQL 資料庫中。本文描述儲存 JSON 文件的選項。

JSON 儲存體格式

第一個儲存體設計決策是如何將 JSON 文件儲存在資料表中。有兩個可用的選項：

LOB 儲存體 - JSON 文件可以依現況儲存於資料行，其資料類型為 json 或 nvarchar。這是快速載入資料和內嵌的最佳方式，因為載入速度與字串資料行的載入速度相符。如果未針對 JSON 值編製索引，這種方法可能會對查詢/分析時間造成額外的效能影響，因為原始 JSON 文件必須在查詢執行時進行剖析。
關聯式儲存體 - JSON 文件可以在使用 OPENJSON、JSON_VALUE 或 JSON_QUERY 函式插入資料表時進行剖析。輸入 JSON 文件的片段，可以儲存在包含 JSON 子元素且資料類型為 json 或 nvarchar 的資料行。這種方法會增加載入時間，因為載入期間要進行 JSON 剖析；不過，查詢效能與關聯式資料的傳統查詢效能相符。
目前在 SQL Server 中，JSON 並非內建資料類型。

備註

JSON 資料類型：

適用於具有 SQL Server 2025 或 隨時保持最新更新原則的 Azure SQL 資料庫和 Azure SQL 受控執行個體。
目前正處於 SQL Server 2025（17.x）的預覽版，並支援 Fabric 中的 SQL 資料庫。

經典資料表

將 JSON 文件儲存在 SQL Server 或 Azure SQL 資料庫的最簡單方式是建立雙資料行資料表，其中包含文件的識別碼和文件的內容。例如：

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

或者，在有支援的情況下：

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

這個結構相等於您可以在傳統文件資料庫中找到的集合。主索引鍵 _id 是一個自動遞增的值，它為每份文件提供唯一的識別碼，而且可供快速查閱。此結構對於您要依識別碼擷取文件或依識別碼更新儲存文件的傳統 NoSQL 案例而言是不錯的選擇。

使用可用於儲存 JSON 文件的原生 json 資料類型。
nvarchar(max) 資料類型可讓您儲存大小高達 2 GB 的 JSON 文件。不過如果您確定 JSON 文件不大於 8 KB，則基於效能考量，我們建議您使用 nvarchar(4000)，不要使用 nvarchar(max)。

在上述範例中建立的範例資料表假設，有效的 JSON 文件會儲存在 log 資料行。如果您想要確定有效的 JSON 會儲存在 log 資料行，您可以新增資料行的 CHECK 條件約束。例如：

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

每次有人插入或更新資料表中的文件時，這個條件約束會驗證 JSON 文件已正確格式化。如果沒有條件約束，資料表最適合插入，因為任何 JSON 文件會直接新增到資料行，而不進行任何處理。

當您將 JSON 文件儲存到資料表中時，您可以使用標準的 Transact-SQL 語言來查詢文件。例如：

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

有一項強大的優點，就是您可以使用「任何」T-SQL 函式和查詢子句來查詢 JSON 文件。 SQL Server 和 SQL Database 不會導入查詢中您可用來分析 JSON 文件的任何條件約束。您可以使用 JSON_VALUE 函式從 JSON 文件擷取值，並像任何其他值將其用於查詢。

可使用豐富 T-SQL 查詢語法這項功能是 SQL Server 和 SQL Database 與傳統 NoSQL 資料庫之間的主要差異；在 Transact-SQL 中，您可能有處理 JSON 資料所需的任何函式。

Indexes

如果您發現您的查詢經常以某個屬性來搜尋文件 (例如，JSON 文件中的 severity 屬性)，您可以在屬性上新增資料列存放區非叢集索引索引以加速查詢。

您可以建立計算資料行，從指定的路徑 (也就是在路徑 $.severity) 上的 JSON 資料行公開 JSON 值，並在這個計算資料行上建立標準索引。例如：

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

此範例中使用的計算資料行是非永久存儲或虛擬的，不會為資料表增加額外空間。它由索引 ix_severity 用來改善查詢的效能，如下列範例所示：

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

此索引的一項重要特性是支援定序。如果原始的 nvarchar 資料行有 COLLATION 屬性 (例如，區分大小寫或日文)，則索引組織會根據語言規則或與 NVARCHAR 資料行建立關聯的區分大小寫規則。如果您正在為需要使用自訂語言規則來處理 JSON 文件的全球市場開發應用程式，這個感知定序可能是一項重要功能。

大型資料表和列存儲格式

如果您預期在集合中有大量的 JSON 文件，我們建議在集合上新增叢集資料行存放區索引，如下列範例所示：

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] nvarchar(max),
    INDEX cci CLUSTERED COLUMNSTORE
);

叢集資料行存放區索引能提供高資料壓縮 (最多 25 倍)，大幅減少您的儲存空間需求、降低儲存體的成本，以及提升您的工作負載 I/O 效能。此外，叢集資料行存放區索引針對您 JSON 文件上的資料表掃描和分析而最佳化，因此這種類型的索引可能是記錄分析的最佳選項。

上述範例中使用序列物件將值指派給 _id 資料行。序列號碼和身分都是ID欄位的有效選項。

經常變更的文件和經記憶體最佳化的資料表

如果您預期在集合中會有大量的更新、插入和刪除作業，您可以在記憶體最佳化資料表中儲存 JSON 文件。記憶體最佳化的 JSON 集合一律會將資料保存在記憶體中，因此沒有儲存體 I/O 額外負荷。此外，記憶體最佳化 JSON 集合完全無鎖定，也就是對文件採取的動作不會封鎖任何其他作業。

要將傳統集合轉換成記憶體最佳化的集合，您唯一要做的是在資料表定義之後指定 WITH (MEMORY_OPTIMIZED=ON) 選項，如下列範例所示。然後，您便有記憶體最佳化版本的 JSON 集合。

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)

記憶體最佳化的資料表是最適合經常變更之文件的選項。當您考慮記憶體最佳化資料表時，也請考慮效能。可能的話，請針對記憶體最佳化集合中的 JSON 文件使用 nvarchar(4000)，不使用 nvarchar(max)，因為它可能會大幅提升效能。記憶體最佳化的資料表不支援 json 資料類型。

如同傳統的資料表，您可以使用計算欄位對記憶體最佳化資料表中所公開的欄位新增索引。例如：

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max),

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

為了充分發揮效能，將 JSON 值轉換成可用來保存屬性值的最小可能類型。在上述範例中，使用了 tinyint。

您也可以將更新 JSON 文件的 SQL 查詢，放在預存程序中以善加利用原生編譯。例如：

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

此原生編譯的程式會接受查詢，並建立執行該查詢的 .DLL 程式碼。原生編譯的程序是能更快速查詢和更新資料的方法。

Conclusion

SQL Server 和 SQL Database 中的原生 JSON 函式，讓您能像在 NoSQL 資料庫中一樣地處理 JSON 文件。每個資料庫不論是關聯式還是 NoSQL，都有一些 JSON 資料處理方面的優缺點。將 JSON 文件儲存在 SQL Server 或 SQL Database 的主要優點是 SQL 語言的完整支援。您可以使用豐富的 Transact-SQL 語言來處理資料，以及設定各種不同的儲存體選項 (從高壓縮和快速分析用的資料行存放區索引，到無鎖定處理用的經記憶體最佳化的資料表)。同時，您會受益於成熟的安全性和國際化功能，並且可以輕鬆地重複用於 NoSQL 案例中。本文中所描述的理由是考慮將 JSON 文件儲存在 SQL Server 或 SQL Database 中的絕佳原因。

深入瞭解 SQL Database Engine 中的 JSON

如需內建 JSON 支援的視覺效果簡介，請參閱下列影片：

NoSQL 與關聯式領域之間的橋樑 JSON

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-18