Verwenden von Tabellenwertparameter (Datenbank-Engine)

  • Artikel
  • 3 Minuten Lesedauer

Gilt für: SQL Server Azure SQL Datenbank Azure Synapse Analytics Analytics Platform System (PDW)

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

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

Transact-SQL übergibt Tabellenwertparameter an Routinen, um eine Kopie der Eingabedaten zu vermeiden. Sie können Transact-SQL-Routinen mit tabellenwertigen Parametern erstellen und ausführen und sie von Transact-SQL-Code, verwalteten und nativen Clients in jeder verwalteten Sprache aufrufen.

In diesem Thema:

Vorteile

Einschränkungen

Tabellenwertparameter und BULK INSERT-Vorgänge

Beispiel

Vorteile

Ein Tabellenwertparameter wird auf die gespeicherte Prozedur, Funktion oder dynamische Transact-SQL-Text ausgerichtet, genau wie andere Parameter. 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 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) werden tabellenwertige Parameter auch für parameterisierte 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-Berechtigung für den Typ oder für das Schema bzw. die Datenbank, das/die den Typ enthält, verfügen.

Beschränkungen

Für Tabellenwertparameter gelten die folgenden Einschränkungen:

  • SQL Server verwaltet keine Statistiken über Spalten von Tabellenwertparametern.
  • Tabellenwertparameter müssen als Eingabe-READONLY-Parameter 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 1000 Zeilen.

Wiederverwendete Tabellenparameter nutzen den Zwischenspeicher für temporäre Tabellen. Diese Zwischenspeicherung ermöglicht eine bessere Skalierbarkeit als vergleichbare BULK INSERT-Vorgänge. Bei kleineren Vorgängen zum Einfügen von Zeilen können Sie u. U. eine bessere Leistung erzielen, wenn Sie Parameterlisten oder Batch-Anweisungen statt BULK INSERT-Vorgänge oder Tabellenwertparameter verwenden. 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.

Beispiel

Im folgenden Beispiel wird Transact-SQL verwendet und gezeigt, wie Sie einen Tabellenwertparametertyp erstellen, eine Variable deklarieren, auf diese verweisen, die Parameterliste füllen und dann die Werte an eine gespeicherte Prozedur in der AdventureWorks-Datenbank ü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 AdventureWorks2012.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 AdventureWorks2012.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Siehe auch