Freigeben über

Speichern von JSON-Dokumenten

Gilt für: SQL Server 2016 (13.x) und spätere Versionen Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Die SQL-Datenbank-Engine verfügt über native JSON-Funktionen, mit denen Sie JSON-Dokumente durch die standardmäßige SQL-Sprache analysieren können. Sie können JSON-Dokumente im SQL-Datenbankmodul speichern und JSON-Daten wie in einer NoSQL-Datenbank abfragen. Dieser Artikel beschreibt die Optionen zum Speichern von JSON-Dokumenten.

JSON-Speicherformat

Die erste Entscheidung zum Speicherentwurf betrifft das Speichern von JSON-Dokumenten in den Tabellen. Es stehen zwei Optionen zur Verfügung:

  • LOB-Speicher – JSON-Dokumente können as-is in Spalten mit dem Datentyp JSON oder nvarchar gespeichert werden. Dies ist die beste Möglichkeit für schnelles Laden von Daten und Erfassen, da die Ladegeschwindigkeit dem Laden von Zeichenfolgenspalten entspricht. Dieser Ansatz kann zu einer zusätzlichen Leistungseinbuße bei der Abfrage-/Analysezeit führen, wenn die Indizierung von JSON-Werten nicht durchgeführt wird, da die unformatierten JSON-Dokumente analysiert werden müssen, während die Abfragen ausgeführt werden.
  • Relationaler Speicher – JSON-Dokumente können analysiert werden, während sie in die Tabelle mithilfe der Funktionen OPENJSON, JSON_VALUE oder JSON_QUERY eingefügt werden. Fragmente aus den EINGABE-JSON-Dokumenten können in den Spalten gespeichert werden, die JSON-Unterelemente mit Datentypen json oder nvarchar enthalten. Diese Vorgehensweise erhöht die Ladezeit, da die JSON-Analyse beim Laden erfolgt. Die Abfrageleistung entspricht jedoch der Leistung bei klassischen Abfragen relationaler Daten.
  • JSON ist derzeit kein integrierter Datentyp in SQL Server.

Hinweis

Der JSON-Datentyp:

  • ist allgemein für Azure SQL-Datenbank und Azure SQL Managed Instance mit der SQL Server 2025- oder Always-up-to-dateAktualisierungsrichtlinie verfügbar.
  • befindet sich in der Vorschau für SQL Server 2025 (17.x) und die SQL-Datenbank in Fabric.

Klassische Tabellen

Die einfachste Möglichkeit zum Speichern von JSON-Dokumenten in SQL Server oder Azure SQL-Datenbank stellt die Erstellung einer zweispaltigen Tabelle dar, die die ID und den Inhalt des Dokuments enthält. Beispiel:

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

Oder, soweit unterstützt:

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

Diese Struktur entspricht den Sammlungen, die Sie in klassischen Dokumentdatenbanken finden. Der Primärschlüssel _id ist ein automatisch inkrementierter Wert, der einen eindeutigen Bezeichner für jedes Dokument bereitstellt und schnelle Suchvorgänge ermöglicht. Diese Struktur ist eine gute Wahl für die klassischen NoSQL-Szenarios, in denen Sie ein Dokument mit der ID abrufen oder ein gespeichertes Dokument mit einer bestimmten ID aktualisieren möchten.

  • Verwenden Sie den nativen JSON-Datentyp , in dem JSON-Dokumente gespeichert werden können.
  • Mit dem Datentyp "nvarchar(max)" können Sie JSON-Dokumente mit einer Größe von bis zu 2 GB speichern. Wenn Sie sicher sind, dass Ihre JSON-Dokumente nicht größer als 8 KB sind, empfehlen wir jedoch, nvarchar(4000) anstelle von nvarchar(max) aus Leistungsgründen zu verwenden.

Bei der im vorherigen Beispiel erstellten Beispieltabelle wird davon ausgegangen, dass gültige JSON-Dokumente in der Spalte log gespeichert sind. Wenn Sie sichergehen wollen, dass ein gültiges JSON-Objekt in der Spalte log gespeichert ist, können Sie für die Spalte eine CHECK-Einschränkung hinzufügen. Beispiel:

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

Jedes Mal, wenn ein Dokument in die Tabelle eingefügt oder in der Tabelle aktualisiert wird, überprüft diese Einschränkung, ob das JSON-Dokument korrekt formatiert ist. Ohne diese Einschränkung ist die Tabelle für Einfügungen optimiert, da jedes JSON-Dokument ohne weitere Verarbeitung direkt zur Spalte hinzugefügt wird.

Wenn Sie die JSON-Dokumente in der Tabelle speichern, können Sie zum Abfragen der Dokumente die Standardsprache Transact-SQL verwenden. Beispiel:

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

Es ist ein leistungsstarker Vorteil, dass Sie jede T-SQL-Funktion und Abfrageklausel verwenden können, um JSON-Dokumente abzufragen. SQL Server und SQL-Datenbank führen in den Abfragen, die Sie zur Analyse von JSON-Dokumenten verwenden können, keine Einschränkungen ein. Sie können mit der Funktion JSON_VALUE Werte aus einem JSON-Dokument extrahieren und wie jeden anderen Wert in der Abfrage verwenden.

Diese Fähigkeit, umfassende T-SQL-Abfragesyntax zu verwenden, ist der entscheidende Unterschied zwischen SQL Server und SQL-Datenbank und klassischen NoSQL-Datenbanken – bei Transact-SQL sind aller Wahrscheinlichkeit nach alle Funktionen vorhanden, die Sie zur Verarbeitung von JSON-Daten benötigen.

Indexes

Wenn Sie feststellen, dass Ihre Abfragen häufig Dokumente nach bestimmten Eigenschaften durchsuchen (z.B. nach einer severity-Eigenschaft in einem JSON-Dokument), können Sie einen klassischen nicht gruppierten Index für die Eigenschaft hinzufügen, um die Abfragen zu beschleunigen.

Sie können eine berechnete Spalte erstellen, die JSON-Werte von den JSON-Spalten für den angegebenen Pfad (d.h. für den Pfad $.severity) verfügbar macht, und einen Standardindex für diese berechnete Spalte erstellen. Beispiel:

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

Bei der in diesem Beispiel verwendeten berechneten Spalte handelt es sich um eine nicht persistierte oder virtuelle Spalte, die den Speicherplatz der Tabelle nicht erweitert. Diese wird vom Index ix_severity verwendet, um wie im folgenden Beispiel die Leistung von Abfragen zu verbessern:

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

Ein wichtiges Merkmal dieses Index ist, dass er sich sortieren lässt. Wenn die ursprüngliche Spalte 'nvarchar' eine COLLATION Eigenschaft (z. B. Groß-/Kleinschreibungssensitivität oder japanische Spracheigenschaft) aufweist, wird der Index entsprechend den Sprachregeln oder den Groß-/Kleinschreibungsregeln organisiert, die der Spalte 'nvarchar' zugeordnet sind. Die Unterstützung dieser Sortierung kann sich für die Entwicklung von Anwendungen für globale Märkte, die benutzerdefinierte Sprachregeln bei der Verarbeitung von JSON-Dokumenten erfordern, als wichtiges Feature erweisen.

Große Formate für Tabellen und Columnstore-Indizes

Wenn Sie mit einer großen Anzahl von JSON-Dokumenten in Ihrer Sammlung rechnen, empfehlen wir Ihnen, einen gruppierten Columnstore-Index für die Sammlung hinzuzufügen, wie im folgenden Beispiel gezeigt wird:

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
);

Ein gruppierter Columnstore-Index ermöglicht eine hohe (bis zu 25-fache) Datenkomprimierung, wodurch Sie Ihren Speicherplatzbedarf deutlich reduzieren, die Speicherkosten senken und die E/A-Leistung Ihrer Workloads erhöhen können. Außerdem sind gruppierte Columnstore-Indizes für Tabellenscans und Analysen für Ihre JSON-Dokumente optimiert, sodass dieser Indextyp die beste Option für Protokollanalysen darstellen kann.

Im vorhergehende Beispiel wird ein Sequenzobjekt verwendet, um der Spalte _id Werte zuzuweisen. Sowohl Sequenzen als auch Identitäten sind gültige Optionen für die ID-Spalte.

Häufig geänderte Dokumente und speicheroptimierte Tabellen

Wenn Sie mit einer großen Anzahl von Aktualisierungs-, Einfüge- und Löschvorgängen in Ihren Sammlungen rechnen, können Sie Ihre JSON-Dokumente in speicheroptimierten Tabellen speichern. Speicheroptimierte JSON-Sammlungen verwalten die Daten immer im Arbeitsspeicher, sodass kein Speicher-E/A-Overhead erzeugt wird. Darüber hinaus sind speicheroptimierte JSON-Sammlungen komplett sperrenfrei, d. h., andere Vorgänge werden durch Aktionen für Dokumente nicht blockiert.

Um eine klassische Sammlung in eine speicheroptimierte Sammlung zu konvertieren, müssen Sie lediglich die Option WITH (MEMORY_OPTIMIZED=ON) nach der Tabellendefinition angeben, wie im folgenden Beispiel gezeigt wird. Dann haben Sie eine speicheroptimierte Version der JSON-Sammlung erstellt.

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

Eine speicheroptimierte Tabelle ist die beste Option für häufig geänderte Dokumente. Wenn Sie speicheroptimierte Tabellen in Erwägung ziehen, sollten Sie auch die Leistung berücksichtigen. Verwenden Sie den nvarchar(4000) -Datentyp anstelle von nvarchar(max) für JSON-Dokumente in Ihren speicheroptimierten Auflistungen, da die Leistung möglicherweise drastisch verbessert wird. Der JSON-Datentyp wird bei speicheroptimierten Tabellen nicht unterstützt.

Wie bei klassischen Tabellen können Sie durch berechnete Spalten Indizes für die Felder, die Sie in speicheroptimierten Tabellen verfügbar machen, hinzufügen. Beispiel:

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)

Um die Leistung zu maximieren, sollten Sie den JSON-Wert in den kleinstmöglichen Typ umwandeln, der zum Speichern des Eigenschaftswerts verwendet werden kann. Im vorherigen Beispiel wird tinyint verwendet.

Sie können auch SQL-Abfragen, die JSON-Dokumente aktualisieren, in gespeicherte Prozeduren einbinden, um von den Vorteilen der nativen Kompilierung zu profitieren. Beispiel:

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

Diese nativ kompilierte Prozedur erstellt anhand der Abfrage einen DLL-Code, der die Abfrage ausführt. Mit einer nativ kompilierten Prozedur können Sie schneller Daten abfragen und aktualisieren.

Conclusion

Durch native JSON-Funktionen in SQL Server und SQL-Datenbank können JSON-Dokumente wie in NoSQL-Datenbanken verarbeitet werden. Datenbanken – ganz gleich, ob relationale oder NoSQL-Datenbanken – weisen Vor- und Nachteile hinsichtlich der Verarbeitung von JSON-Daten auf. Der größte Vorteil bei der Speicherung von JSON-Dokumenten in SQL Server oder SQL-Datenbank besteht in der vollständigen Unterstützung für SQL-Sprachen. Sie können mithilfe der umfangreichen Transact-SQL-Sprache Daten verarbeiten und eine Vielzahl von Speicheroptionen konfigurieren, von Columnstore-Indizes für umfangreiche Komprimierungen und schnelle Analysen bis hin zu speicheroptimierten Tabellen für eine Verarbeitung ohne Sperren. Gleichzeitig profitieren Sie von ausgereiften Sicherheits- und Internationalisierungsfeatures, die Sie in Ihrem NoSQL-Szenario einfach wiederverwenden können. Die in diesem Artikel beschriebenen Gründe sind überzeugende Argumente dafür, die Speicherung von JSON-Dokumenten in SQL Server oder SQL-Datenbank in Erwägung zu ziehen.

Weitere Informationen zu JSON im SQL-Datenbankmodul

Eine visuelle Einführung in die integrierte JSON-Unterstützung finden Sie in den folgenden Videos:

Verwandte Inhalte

  • Last updated on