Verwenden von Tabellenwertparameter (Datenbankmodul)

Tabellenwertparameter werden mit benutzerdefinierten Tabellentypen deklariert. Mit Tabellenwertparametern können Sie mehrere Datenzeilen an eine Transact-SQL-Anweisung oder an eine Routine übergeben, z. B. eine gespeicherte Prozedur oder eine Funktion, 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 mit 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, verwaltete und Native Clients in jeder beliebigen verwalteten Sprache aufrufen.

In diesem Thema:

Vorteile

Einschränkungen

Tabellenwertparameter und BULK INSERT-Vorgänge

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

[Top]

Einschränkungen

Für Tabellenwertparameter gelten die folgenden Einschränkungen:

• In SQL Server werden keine Spaltenstatistiken für Tabellenwertparameter verwaltet.

• 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.

[Top]

Tabellenwertparameter undBULK 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.

[Top]

Beispiel

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 ü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

[Top]

Siehe auch

Verweis

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)

CREATE FUNCTION (Transact-SQL)