Tabellenwertparameter als klare SQL-Server-Schnittstelle

Ein Datenbankaufruf pro Bestellposition erzeugt unnötige Netzwerkrunden und erschwert Fehlerbehandlung. Eine kommagetrennte Zeichenfolge verschiebt die Schwierigkeit in Parser und Maskierung. Tabellenwertparameter, kurz TVP, übertragen eine typisierte Zeilenmenge mit einem Prozeduraufruf.

Wichtig ist der Vertrag: Handelt es sich um eindeutige Produkte, um Positionen mit erlaubten Wiederholungen oder um unabhängige Operationen? Diese Entscheidung bestimmt Schlüssel, Prüfung, Zuordnung und Wiederholungsverhalten.

Den Eingabetyp fachlich definieren

Die Übung akzeptiert eine positive Menge pro Produkt und meldet unbekannte Produkte ausdrücklich.

-- Disposable practice database. Send GO-separated batches separately.
CREATE TYPE dbo.RequestLinesDemo AS TABLE (
    ProductId int NOT NULL PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity > 0)
);
GO
CREATE TABLE dbo.ProductsTvpDemo(ProductId int PRIMARY KEY, UnitPrice decimal(12,2));
INSERT dbo.ProductsTvpDemo VALUES (1,10.00),(2,15.00);
GO
CREATE PROCEDURE dbo.ValidateLinesDemo @Lines dbo.RequestLinesDemo READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT l.ProductId, l.Quantity, p.UnitPrice,
           CONVERT(bit, CASE WHEN p.ProductId IS NULL THEN 0 ELSE 1 END) AS IsValid
    FROM @Lines AS l
    LEFT JOIN dbo.ProductsTvpDemo AS p ON p.ProductId = l.ProductId
    ORDER BY l.ProductId;
END;
GO

Der Primärschlüssel verbietet doppelte ProductId-Werte. Sind wiederholte Produkte fachlich getrennte Positionen, verwenden Sie stattdessen LineId als Schlüssel. Echte Positionen dürfen nicht allein wegen einer mengenorientierten Schnittstelle zusammenfallen.

CHECK verlangt positive Mengen und NOT NULL verhindert unbekannte Werte. Solche Typregeln prüfen Form und einfache Bedingungen, ersetzen aber keine Prüfungen gegen aktuelle Produktdaten, Status oder Verfügbarkeit.

READONLY ist für den TVP-Parameter erforderlich. Die Prozedur kann lesen und verknüpfen, aber die Eingabe nicht wie eine veränderbare temporäre Tabelle aktualisieren. Benötigte Transformationen gehören in eine eigene Arbeitstabelle, deren Aufwand berücksichtigt werden muss.

Der Aufruf enthält ein unbekanntes Produkt.

DECLARE @Input dbo.RequestLinesDemo;
INSERT @Input VALUES (1,2),(2,3),(99,1);
EXEC dbo.ValidateLinesDemo @Lines = @Input;

Produkte 1 und 2 sind gültig, 99 wird markiert. LEFT JOIN erhält die fehlerhafte Eingabe. Ein INNER JOIN würde sie verbergen und könnte eine Teilausgabe wie eine vollständige erfolgreiche Prüfung erscheinen lassen.

Prüfung und Ausführung zusammenpassen lassen

Die Prozedur demonstriert Validierung, keine abgeschlossene Bestelltransaktion. Entscheiden Sie für die spätere Schreiboperation, ob eine falsche Position die ganze Anfrage verwirft oder Teilannahme erlaubt ist. Jede Ausgabe braucht eine eindeutige Eingabezuordnung sowie einen verständlichen Gesamtstatus.

Eine erfolgreiche Vorprüfung friert Produktzustände nicht ein. Preise, Verfügbarkeit und Rechte können sich vor der nächsten Anfrage ändern. Relevante Bedingungen müssen in der tatsächlichen Transaktion erneut geprüft oder über eine geeignete Version abgesichert werden.

TVPs besitzen keine garantierte Zeilenreihenfolge. Wenn sie wichtig ist, übertragen Sie eine Sequenzspalte und sortieren Sie Ergebnisse ausdrücklich. Neue Zielkennungen sollten zusammen mit der ursprünglichen LineId zurückgegeben werden, statt anhand zufälliger Ausgabereihenfolge zugeordnet zu werden.

Ein .NET-Client bindet einen strukturierten Parameter mit korrektem schemaqualifiziertem Typnamen. DataTable oder ein Zeilenstrom können die Eingabe liefern. Stimmen Sie Datentypen, Genauigkeit, Skala, Länge und NULL-Regeln ab. Ein richtiger Parametername allein garantiert keine passende Struktur.

Mit realistischen Mengen messen

SQL Server pflegt keine Spaltenstatistiken für TVPs. Fünf Eingabezeilen und fünfzigtausend können dennoch sehr unterschiedliche Pläne benötigen. Ein Primärschlüssel liefert Eindeutigkeit und Zugriffsmöglichkeiten, aber kein Verteilungshistogramm.

Bei großen oder stark schwankenden Mengen kann eine Kopie in eine temporäre Tabelle mit passenden Indizes und Statistiken helfen. Sie verursacht zusätzliche Kopierarbeit und tempdb-Last. Messen Sie daher den gesamten Aufruf statt nur die abschließende Abfrage. Recompilation hilft eventuell bei Mengenunterschieden, erzeugt aber keine fehlenden Verteilungsstatistiken.

Begrenzen Sie Anfragegrößen und erwägen Sie mehrere kontrollierte Batches für große Importe. Ein TVP ist nicht bei jeder Menge schneller als Bulk Loading. Serialisierung, Netzwerk, Kompilierung und Ausführung gehören gemeinsam in den Vergleich.

Der Aufrufer benötigt passende Prozedur- und Typrechte, gegebenenfalls einschließlich REFERENCES. Trennen Sie Bereitstellungsrechte von normaler Nutzung. Änderungen eines benutzerdefinierten Tabellentyps verlangen üblicherweise eine abgestimmte Versionierung abhängiger Objekte und älterer Clients.

Prüfen Sie leere Eingaben, Dubletten, ungültige Mengen, unbekannte Produkte, maximale Größe und Wiederholung nach unklarer Antwort. So reduziert die Schnittstelle Netzwerkrunden, ohne die Bedeutung einzelner Positionen oder der gesamten Anfrage zu verlieren.

Technische Referenzen: Microsoft Learn: Table-valued parameters · Microsoft Learn: CREATE TYPE.