Freigeben über

Verwendung von Table-Valued-Parametern (Datenbank-Engine)

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.

In diesem Thema:

Vorteile

Einschränkungen

Table-Valued Parameter im Vergleich zu BULK INSERT-Vorgängen

Beispiel

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 werden auch Tabellenwertparameter für parametrisierte Abfragen zwischengespeichert.

Einschränkungen

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.

  • Sie können keinen Tabellenwertparameter als Ziel einer SELECT INTO- oder INSERT EXEC-Anweisung verwenden. Ein Tabellenwertparameter kann sich in der FROM-Klausel von SELECT INTO oder in der INSERT EXEC-Zeichenfolge oder gespeicherten Prozedur befinden.

Table-Valued Parameter im Vergleich mit BULK INSERT-Vorgängen

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 mit höheren Startkosten als Tabellenwertparametern sind tabellenwertige Parameter gut geeignet, um weniger als 1000 Zeilen einzufügen.

Wiederverwendete Tabellenparameter nutzen den Zwischenspeicher für temporäre Tabellen. Diese Tabellenzwischenspeicherung ermöglicht eine bessere Skalierbarkeit als gleichwertige BULK INSERT-Vorgänge. Durch die Verwendung von kleinen Zeileneinfügevorgängen kann ein kleiner Leistungsvorteil durch Verwendung von Parameterlisten oder Batchanweisungen anstelle von BULK INSERT-Vorgängen oder tabellenwertigen Parametern erzielt 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.

Beispiel

Im folgenden Beispiel wird Transact-SQL verwendet und veranschaulicht, wie Sie einen Parametertyp mit Tabellenwert erstellen, eine Variable deklarieren, um darauf zu verweisen, die Parameterliste auszufüllen und dann die Werte an eine gespeicherte Prozedur zu übergeben.

USE AdventureWorks2012;  
GO  
  
/* 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;  
GO

Siehe auch

CREATE TYPE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
sys.types (Transact-SQL)
sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
FUNKTION ERSTELLEN (Transact-SQL)

  • Last updated on