Artikel
09/24/2010

Tabellenwertparameter (Datenbankmodul)

Bei Tabellenwertparametern handelt es sich um einen neuen Parametertyp in SQL Server 2008. 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.

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

Erstellen und Verwenden von Tabellenwertparametern in Transact-SQL

Tabellenwertparameter verfügen über zwei Hauptkomponenten: einen SQL Server-Typ und einen Parameter, der auf diesen Typ verweist. Gehen Sie wie folgt vor, um Tabellenwertparameter zu erstellen und zu verwenden:

Erstellen Sie einen Tabellentyp, und definieren Sie die Tabellenstruktur.

Informationen zum Erstellen eines SQL Server-Typs finden Sie unter Benutzerdefinierte Tabellentypen. Weitere Informationen zum Definieren einer Tabellenstruktur finden Sie unter CREATE TABLE (Transact-SQL).
Deklarieren Sie eine Routine mit einem Parameter des Tabellentyps. Weitere Informationen zu SQL Server-Routinen finden Sie unter CREATE PROCEDURE (Transact-SQL) und CREATE FUNCTION (Transact-SQL).
Deklarieren Sie eine Variable vom Tabellentyp, und verweisen Sie auf den Tabellentyp. Weitere Informationen zum Deklarieren von Variablen finden Sie unter DECLARE @local\_variable (Transact-SQL).
Weisen Sie der Tabellenvariablen mit einer INSERT-Anweisung einen Wert zu. Weitere Informationen über das Einfügen von Daten finden Sie unter Hinzufügen von Zeilen mithilfe von INSERT und SELECT.
Nachdem Sie die Tabellenvariable erstellt und ihr einen Wert zugewiesen haben, können Sie die Variable an eine Routine übergeben.

Wenn die Routine den Bereich verlässt, ist der Tabellenwertparameter nicht mehr verfügbar. Die Typdefinition bleibt erhalten, bis sie gelöscht wird.

Informationen zur Verwendung eines Tabellenwertparameters im SQL Server Native Client finden Sie unter Tabellenwertparameter (SQL Server Native Client).

Informationen zur Verwendung eines Tabellenwertparameters in ADO.NET finden Sie in der Dokumentation zu ADO.NET.

Vorteile

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

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.

Bereich

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.

Sicherheit

Die Berechtigungen für Tabellenwertparameter orientieren sich am Objektsicherheitsmodell für SQL Server, indem die folgenden Transact-SQL-Schlüsselwörter verwendet werden: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION und REVOKE.

Katalogsichten

Informationen zu Tabellenwertparametern erhalten Sie durch Abfrage der folgenden Katalogsichten: sys.parameters (Transact-SQL), sys.types (Transact-SQL) und sys.table_types (Transact-SQL).

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

Die folgende Tabelle zeigt, welche Technologie basierend auf der Geschwindigkeit der Einfügevorgänge am geeignetsten ist.

Datenquelle	Serverlogik	Zeilenanzahl	Optimale Technologie
Formatierte Datendatei auf dem Server	Direkte Einfügung	< 1000	BULK INSERT
Formatierte Datendatei auf dem Server	Direkte Einfügung	> 1000	BULK INSERT
Formatierte Datendatei auf dem Server	Komplex	< 1000	Tabellenwertparameter
Formatierte Datendatei auf dem Server	Komplex	> 1000	BULK INSERT
Remoteclientprozess	Direkte Einfügung	< 1000	Tabellenwertparameter
Remoteclientprozess	Direkte Einfügung	> 1000	BULK INSERT
Remoteclientprozess	Komplex	< 1000	Tabellenwertparameter
Remoteclientprozess	Komplex	> 1000	Tabellenwertparameter

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

USE AdventureWorks;
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 usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks].[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 
    [AdventureWorks].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

Siehe auch

Verweis

CREATE TYPE (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

Freigeben über