Verwenden von Tabellenwertparameter (Datenbank-Engine)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Tabellenwertparameter werden mit benutzerdefinierten Tabellentypen deklariert. Mit Tabellenwertparametern können Sie mehrere Datenzeilen an eine Transact-SQL-Anweisung oder an eine Routine, z. B. eine gespeicherte Prozedur oder eine Funktion, übergeben, ohne eine temporäre Tabelle oder viele Parameter erstellen zu müssen.

Tabellenwertparameter entsprechen Parameterarrays in OLE DB und ODBC, bieten jedoch eine größere Flexibilität und eine engere Integration in Transact-SQL. Ein weiterer Vorteil von Tabellenwertparametern besteht darin, dass sie in setbasierten Vorgängen verwendet werden können.

Transact-SQL übergibt Tabellenwertparameter mittels Verweise an Routinen, sodass keine Kopie der Eingabedaten erstellt werden muss. Sie können Transact-SQL-Routinen mit Tabellenwertparametern erstellen und ausführen und diese über Transact-SQL-Code sowie über verwaltete und native Clients in jeder beliebigen verwalteten Sprache aufrufen.

Vorteile

Der Bereich eines Tabellenwertparameters entspricht wie auch bei anderen Parametern der gespeicherten Prozedur, der Funktion oder dem dynamischen Transact-SQL-Text. Ebenso entspricht der Bereich einer Tabellentypvariablen dem Bereich einer beliebigen lokalen Variablen, die mit einer DECLARE-Anweisung erstellt wurde. Sie können Tabellenwertvariablen in dynamischen Transact-SQL-Anweisungen deklarieren und diese Variablen dann als Tabellenwertparameter an gespeicherte Prozeduren und Funktionen übergeben.

Tabellenwertparameter bieten mehr Flexibilität und in einigen Fällen auch eine bessere Systemleistung als temporäre Tabellen oder andere Methoden zum Übergeben von Parameterlisten. Tabellenwertparameter bieten die folgenden Vorteile:

  • Erfordern keine Sperren für die erste Auffüllung mit Daten von einem Client
  • Stellen ein einfaches Programmiermodell bereit
  • Ermöglichen die Einbindung komplexer Geschäftslogik in eine einzelne Routine
  • Weniger Roundtrips zum Server
  • Unterstützen Tabellenstrukturen mit unterschiedlicher Kardinalität
  • Weisen eine starke Typbindung auf
  • Ermöglichen die Angabe von Sortierreihenfolge und eindeutigen Schlüsseln über den Client
  • Werden bei der Verwendung in einer gespeicherten Prozedur wie eine temporäre Tabelle zwischengespeichert. Ab SQL Server 2012 (11.x) und neuere Versionen werden Tabellenwertparameter auch für parametrisierte Abfragen zwischengespeichert.

Berechtigungen

Um eine Instanz eines benutzerdefinierten Tabellentyps zu erstellen oder eine gespeicherte Prozedur mit einem Tabellenwertparameter aufzurufen, muss der Benutzer über die EXECUTE und REFERENCES-Berechtigung für den Typ oder für das Schema bzw. die Datenbank, das/die den Typ enthält, verfügen.

Begrenzungen

Für Tabellenwertparameter gelten die folgenden Einschränkungen:

  • SQL Server verwaltet keine Spaltenstatistiken für Tabellenwertparameter.
  • Tabellenwertparameter müssen als READONLY-Eingabeparameter an Transact-SQL-Routinen übergeben werden. Für Tabellenwertparameter im Hauptteil einer Routine können keine DML-Vorgänge wie UPDATE, DELETE oder INSERT durchgeführt werden.
  • Tabellenwertparameter können nicht als Ziel einer SELECT INTO-Anweisung oder einer INSERT EXEC-Anweisung verwendet werden. Tabellenwertparameter können in der FROM-Klausel von SELECT INTO oder in der Zeichenfolge oder gespeicherten Prozedur von INSERT EXEC enthalten sein.

Tabellenwertparameter und BULK INSERT-Vorgänge

Die Verwendung von Tabellenwertparametern ist mit anderen Methoden zur Verwendung setbasierter Variablen vergleichbar. Sehr große Datasets können mit Tabellenwertparametern jedoch häufig schneller verarbeitet werden. Im Vergleich zu Massenvorgängen, bei denen die Startkosten höher sind, eignen sich Tabellenwertparameter optimal zum Einfügen von weniger als 1.000 Zeilen.

Wiederverwendete Tabellenparameter nutzen den Zwischenspeicher für temporäre Tabellen. Diese Zwischenspeicherung ermöglicht eine bessere Skalierbarkeit als vergleichbare Masseneinfügevorgänge. Kleine Operationen zum Einfügen von Zeilen können einen kleinen Leistungsvorteil bringen, wenn Parameterlisten oder Batch-Anweisungen anstelle von BULK INSERT-Operationen oder tabellenwertigen Parametern verwendet werden. Die Programmierung dieser Methoden ist allerdings komplexer, und die Leistung nimmt mit steigender Zeilenanzahl schnell ab.

Tabellenwertparameter eignen sich mindestens so gut wie vergleichbare Parameterarray-Implementierungen.

Beispiele

Im folgenden Beispiel wird Transact-SQL verwendet. Es zeigt, wie Sie einen Tabellenwertparameter erstellen, eine Variable deklarieren, die darauf verweist, Daten in die Parameterliste einfügen und die Werte dann an eine gespeicherte Prozedur in der AdventureWorks-Beispieldatenbank übergeben.

/* Create a table type. */
CREATE TYPE LocationTableType 
   AS TABLE
      ( LocationName VARCHAR(50)
      , CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
   @TVP LocationTableType READONLY
      AS
      SET NOCOUNT ON
      INSERT INTO AdventureWorks2022.Production.Location
         (
            Name
            , CostRate
            , Availability
            , ModifiedDate
         )
      SELECT *, 0, GETDATE()
      FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT Name, 0.00
   FROM AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Der erwartete Ergebnissatz ist:

(181 rows affected)

Zugehöriger Inhalt