Verwenden von Tabellenwertparameter (Datenbankmodul)Use Table-Valued Parameters (Database Engine)

DIESES THEMA GILT FÜR: jaSQL Server (ab 2016)jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Tabellenwertparameter werden mit benutzerdefinierten Tabellentypen deklariert.Table-valued parameters are declared by using user-defined table types. Mit Tabellenwertparametern können Sie mehrere Datenzeilen an eine Transact-SQLTransact-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.You can use table-valued parameters to send multiple rows of data to a Transact-SQLTransact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Tabellenwertparameter entsprechen Parameterarrays in OLE DB und ODBC, bieten jedoch eine größere Flexibilität und eine engere Integration mit Transact-SQLTransact-SQL.Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQLTransact-SQL. Ein weiterer Vorteil von Tabellenwertparametern besteht darin, dass sie in setbasierten Vorgängen verwendet werden können.Table-valued parameters also have the benefit of being able to participate in set-based operations.

Transact-SQLTransact-SQL übergibt Tabellenwertparameter mittels Verweise an Routinen, sodass keine Kopie der Eingabedaten erstellt werden muss. passes table-valued parameters to routines by reference to avoid making a copy of the input data. Sie können Transact-SQLTransact-SQL -Routinen mit Tabellenwertparametern erstellen und ausführen und diese über Transact-SQLTransact-SQL -Code, verwaltete und Native Clients in jeder beliebigen verwalteten Sprache aufrufen.You can create and execute Transact-SQLTransact-SQL routines with table-valued parameters, and call them from Transact-SQLTransact-SQL code, managed and native clients in any managed language.

In diesem Thema:In This Topic:

VorteileBenefits

EinschränkungenRestrictions

Tabellenwertparameter im Vergleich zu BULK INSERT-VorgängenTable-Valued Parameters vs. BULK INSERT Operations

BeispielExample

Vorteile Benefits

Der Bereich eines Tabellenwertparameters entspricht wie auch bei anderen Parametern der gespeicherten Prozedur, der Funktion oder dem dynamischen Transact-SQLTransact-SQL -Text.A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQLTransact-SQL text, exactly like other parameters. Ebenso entspricht der Bereich einer Tabellentypvariablen dem Bereich einer beliebigen lokalen Variablen, die mit einer DECLARE-Anweisung erstellt wurde.Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. Sie können Tabellenwertvariablen in dynamischen Transact-SQLTransact-SQL -Anweisungen deklarieren und diese Variablen dann als Tabellenwertparameter an gespeicherte Prozeduren und Funktionen übergeben.You can declare table-valued variables within dynamic Transact-SQLTransact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.

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.Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Tabellenwertparameter bieten die folgenden Vorteile:Table-valued parameters offer the following benefits:

  • Erfordern keine Sperren für die erste Auffüllung mit Daten von einem ClientDo not acquire locks for the initial population of data from a client.

  • Stellen ein einfaches Programmiermodell bereitProvide a simple programming model.

  • Ermöglichen die Einbindung komplexer Geschäftslogik in eine einzelne RoutineEnable you to include complex business logic in a single routine.

  • Weniger Roundtrips zum ServerReduce round trips to the server.

  • Unterstützen Tabellenstrukturen mit unterschiedlicher KardinalitätCan have a table structure of different cardinality.

  • Weisen eine starke Typbindung aufAre strongly typed.

  • Ermöglichen die Angabe von Sortierreihenfolge und eindeutigen Schlüsseln über den ClientEnable the client to specify sort order and unique keys.

  • Werden bei der Verwendung in einer gespeicherten Prozedur wie eine temporäre Tabelle zwischengespeichert.Are cached like a temp table when used in a stored procedure. Ab SQL Server 2012SQL Server 2012werden Tabellenwertparameter auch für parametrisierte Abfragen zwischengespeichert.Starting with SQL Server 2012SQL Server 2012, table-valued parameters are also cached for parameterized queries.

Einschränkungen Restrictions

Für Tabellenwertparameter gelten die folgenden Einschränkungen:Table-valued parameters have the following restrictions:

  • SQL ServerSQL Server Spaltenstatistiken für Tabellenwertparameter werden nicht verwaltet. does not maintain statistics on columns of table-valued parameters.

  • Tabellenwertparameter müssen als READONLY-Eingabeparameter an Transact-SQLTransact-SQL -Routinen übergeben werden.Table-valued parameters must be passed as input READONLY parameters to Transact-SQLTransact-SQL routines. Für Tabellenwertparameter im Hauptteil einer Routine können keine DML-Vorgänge wie UPDATE, DELETE oder INSERT durchgeführt werden.You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

  • Tabellenwertparameter können nicht als Ziel einer SELECT INTO-Anweisung oder einer INSERT EXEC-Anweisung verwendet werden.You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. Tabellenwertparameter können in der FROM-Klausel von SELECT INTO oder in der Zeichenfolge oder gespeicherten Prozedur von INSERT EXEC enthalten sein.A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

Tabellenwertparameter vs. BULK INSERT-Vorgänge Table-Valued Parameters vs. BULK INSERT Operations

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.Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. Im Vergleich zu Massenvorgängen, bei denen die Startkosten höher sind, eignen sich Tabellenwertparameter optimal zum Einfügen von weniger als 1000 Zeilen.Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

Wiederverwendete Tabellenparameter nutzen den Zwischenspeicher für temporäre Tabellen.Table-valued parameters that are reused benefit from temporary table caching. Diese Zwischenspeicherung ermöglicht eine bessere Skalierbarkeit als vergleichbare BULK INSERT-Vorgänge.This table caching enables better scalability than equivalent BULK INSERT operations. 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.By using small row-insert operations a small performance benefit might be gained by using parameter lists or batched statements instead of BULK INSERT operations or table-valued parameters. Die Programmierung dieser Methoden ist allerdings komplexer, und die Leistung nimmt mit steigender Zeilenanzahl schnell ab.However, these methods are less convenient to program, and performance decreases quickly as rows increase.

Tabellenwertparameter eignen sich mindestens so gut wie vergleichbare Parameterarray-Implementierungen.Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

Beispiel Example

Im folgenden Beispiel wird Transact-SQLTransact-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.The following example uses Transact-SQLTransact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.

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 auchSee Also

CREATE TYPE (Transact-SQL) CREATE TYPE (Transact-SQL)
DEKLARIEREN SIE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
sys.types (Transact-SQL) sys.types (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL) sys.parameter_type_usages (Transact-SQL)
CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)