JSON-Eigenschaften mit berechneten Spalten indizieren

Ein JSON-Dokument ist praktisch, wenn eine Integration flexible Zusatzfelder liefert. Problematisch wird es, wenn häufige Filter den gesamten Text immer wieder für viele Zeilen auswerten. Eine schmale typisierte berechnete Spalte kann die wichtige Eigenschaft einem normalen Index zugänglich machen und das ursprüngliche Dokument erhalten.

Unterscheiden Sie flexible Attribute von fachlichen Schlüsseln. Wenn die Kundenkennung Verknüpfungen, Zugriffsregeln und viele Abfragen bestimmt, benötigt sie einen ausdrücklichen Vertrag. JSON ersetzt weder Datentyp noch Pflichtfeldprüfung oder gültigen Wertebereich.

Die Eigenschaft passend typisieren

Das Beispiel verwendet JSON-Funktionen ab SQL Server 2016 und erstellt permanente Objekte ausschließlich in einer Übungsdatenbank.

-- Create in a disposable practice database.
CREATE TABLE dbo.JsonOrderDemo (
    DocumentId int NOT NULL PRIMARY KEY,
    Payload nvarchar(max) NOT NULL,
    CustomerId AS TRY_CONVERT(bigint, JSON_VALUE(Payload, '$.customerId')) PERSISTED,
    CONSTRAINT CK_JsonOrderDemo_Json CHECK (ISJSON(Payload) = 1),
    CONSTRAINT CK_JsonOrderDemo_Customer CHECK (CustomerId IS NOT NULL AND CustomerId > 0)
);
CREATE INDEX IX_JsonOrderDemo_Customer ON dbo.JsonOrderDemo(CustomerId);
INSERT dbo.JsonOrderDemo(DocumentId, Payload) VALUES
(1, N'{"customerId":42,"status":"new"}'),
(2, N'{"customerId":43,"status":"new"}'),
(3, N'{"customerId":42,"status":"paid"}');
SELECT DocumentId FROM dbo.JsonOrderDemo WHERE CustomerId = 42;

Die erste Abfrage findet Dokumente 1 und 3. CustomerId wird aus dem Dokument als bigint abgeleitet. Der Index enthält damit Zahlen statt langer JSON_VALUE-Texte. Für die winzige Übungstabelle kann ein Scan trotzdem günstiger sein; das Beispiel schafft einen nutzbaren Zugriff, erzwingt aber keinen Plan.

TRY_CONVERT liefert bei einer ungeeigneten Kundenkennung NULL. Die zweite CHECK-Regel verbietet NULL ausdrücklich und verlangt positive Werte. Nur CustomerId > 0 würde UNKNOWN bei NULL zulassen und kein echtes Pflichtfeld bilden.

ISJSON prüft Syntax, nicht das vollständige Geschäftsschema. Ein gültiges Array oder ein Objekt ohne customerId ist deshalb noch keine gültige Bestellung. Die berechnete Spaltenprüfung erkennt den fehlenden numerischen Schlüssel; weitere Pflichtattribute brauchen eigene Regeln.

Die Konvertierung akzeptiert sowohl JSON-Zahlen als auch passende numerische Zeichenfolgen. Soll die Schnittstelle diese Formen unterscheiden, prüfen Sie den Tokentyp beispielsweise mit OPENJSON. Diese Entscheidung sollte bewusst erfolgen und nicht zufällig aus der Typumwandlung entstehen.

Suche und Extraktion abstimmen

Direkte Filter auf CustomerId zeigen den relationalen Zugriff deutlich und geben Parametern einen festen Typ. SQL Server kann auch manche passenden berechneten Ausdrücke erkennen. Kleine Ausdrucksunterschiede können diese Möglichkeit jedoch verändern. Ein stabiler Vertrag ist verlässlicher als viele selbst nachgebildete JSON-Ausdrücke.

JSON-Eigenschaftsnamen werden bei Pfadvergleichen nach Großschreibung unterschieden. customerId und CustomerId sind nicht automatisch identisch, auch wenn gewöhnliche Textvergleiche der Datenbank unempfindlich sind. Prüfen Sie besonders unterschiedliche Benennungsregeln der beteiligten Serialisierer.

JSON_VALUE hat eigene Längengrenzen. Beim herkömmlichen nvarchar-Verfahren kann ein skalarer Wert über 4.000 Zeichen im lax-Modus NULL oder im strict-Modus einen Fehler ergeben. Für unbegrenzte Beschreibungstexte ist das keine geeignete Extraktion. OPENJSON kann solche Inhalte mit passendem Schema bereitstellen.

Indizieren Sie nicht unnötig nvarchar(4000), wenn die Eigenschaft eigentlich eine Zahl oder ein kurzer Code ist. Schlüsselgrenzen und Vergleichskosten bleiben relevant. Umgekehrt kann eine willkürlich kurze Textkonvertierung bedeutende Zeichen abschneiden; prüfen Sie die Länge vor einer solchen Verengung.

Schreibkosten und Weiterentwicklung berücksichtigen

Eine Dokumentänderung berechnet die Eigenschaft neu und pflegt den Index.

UPDATE dbo.JsonOrderDemo
SET Payload = JSON_MODIFY(Payload, '$.customerId', 43)
WHERE DocumentId = 1;
SELECT DocumentId, CustomerId FROM dbo.JsonOrderDemo ORDER BY DocumentId;

Dokument 1 gehört danach zu Kunde 43. Die Anwendung muss keine unabhängige Schattenkennung aktualisieren. Damit entfällt ein Konsistenzrisiko, während Auswertung, Prüfungen, gespeicherte Spalte und Indexpflege Aufwand im Schreibpfad erzeugen.

PERSISTED ist eine Entwurfsentscheidung und keine allgemeine Voraussetzung für jeden Index auf berechneten Spalten. Determinismus, Präzision, unterstützte Typen und notwendige SET-Optionen müssen den Indexregeln entsprechen. Vergleichen Sie Speicher- und Schreibkosten mit eingesparten Lesezugriffen.

Eine Umbenennung oder Typänderung von customerId ist auch bei flexiblem JSON eine Schemamigration. Stimmen Sie Produzent, Prüfung, Extraktion, vorhandene Daten und Leser ab. Ein scheinbar kleiner Wechsel darf alte Werte nicht unbemerkt zu NULL machen oder neue Schreibzugriffe unerwartet ablehnen.

Testen Sie fehlende Schlüssel, falsche Großschreibung, Arrays, ungültiges JSON, nichtnumerische Werte, Überlauf und normale Änderungen. Messen Sie Abfragen und Datenzufuhr zusammen. Der Index ist nur hilfreich, wenn seine fachliche Bedeutung dauerhaft korrekt bleibt.

Technische Referenzen: Microsoft Learn: Index JSON data · Microsoft Learn: JSON_VALUE · Microsoft Learn: ISJSON.