table (Transact-SQL)

Ein bestimmter Datentyp, der zum Speichern eines Resultsets für die spätere Verarbeitung verwendet werden kann. table wird primär zum temporären Speichern einer Reihe von Zeilen verwendet, die als Resultset einer Tabellenwertfunktion zurückgegeben werden. Funktionen und Variablen können mit dem Typ table deklariert werden. table-Variablen können in Funktionen, gespeicherten Prozeduren und Batches verwendet werden. Verwenden Sie zum Deklarieren von Variablen des table-Datentyps die Anweisung DECLARE @local_variable.

Transact-SQL-Syntaxkonventionen

Syntax

table_type_definition ::= 
    TABLE ( { <column_definition> | <table_constraint> } [ ,...n ] ) 

<column_definition> ::= 
    column_name scalar_data_type 
    [ COLLATE <collation_definition> ] 
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ] 
    [ ROWGUIDCOL ] 
    [ column_constraint ] [ ...n ] 

<column_constraint> ::= 
    { [ NULL | NOT NULL ] 
    | [ PRIMARY KEY | UNIQUE ] 
    | CHECK ( logical_expression ) 
    } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
     | CHECK ( logical_expression ) 
     } 

Argumente

• table_type_definition
  Dieselbe Teilmenge von Informationen, die zum Definieren einer Tabelle in CREATE TABLE verwendet wird. Die Tabellendeklaration schließt Spaltendefinitionen, Namen, Datentypen und Einschränkungen ein. Die einzigen zulässigen Einschränkungstypen sind PRIMARY KEY, UNIQUE KEY und NULL.

  Weitere Informationen zur Syntax finden Sie unter CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) und DECLARE @local_variable (Transact-SQL).

• collation_definition
  Die Sortierung einer Spalte, die aus einem Microsoft Windows-Gebietsschema und einer Vergleichsart, einem Windows-Gebietsschema und der Binärschreibweise oder einer Microsoft SQL Server-Sortierung besteht. Wenn collation_definition nicht angegeben ist, erbt die Spalte die Sortierung der aktuellen Datenbank. Wenn die Spalte als CLR-benutzerdefinierter Typ (Common Language Runtime) definiert ist, erbt die Spalte die Sortierung des benutzerdefinierten Typs.

Bewährte Methoden

Verwenden Sie Tabellenvariablen nicht zum Speichern großer Datenmengen (mehr als 100 Zeilen). Planoptionen sind möglicherweise nicht optimal oder stabil, wenn eine Tabellenvariable viele Daten enthält. Schreiben Sie derartige Abfragen ggf. um, sodass sie temporäre Tabellen verwenden, oder verwenden Sie den USE PLAN-Abfragehinweis, um sicherzustellen, dass der Optimierer einen vorhandenen Abfrageplan verwendet, der für das jeweilige Szenario gut funktioniert.

Allgemeine Hinweise

Auf Variablen vom Typ table kann wie im folgenden Beispiel dargestellt anhand des Namens in der FROM-Klausel eines Batches verwiesen werden:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Außerhalb einer FROM-Klausel muss wie im folgenden Beispiel dargestellt ein Alias für Verweise auf table-Variablen verwendet werden:

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

table-Variablen bieten für kleine Abfragen mit Abfrageplänen, die sich nicht ändern, und in Szenarien, in denen Probleme im Zusammenhang mit der Neukompilierung vorrangig sind, die folgenden Vorteile:

• Eine table-Variable verhält sich wie eine lokale Variable. Sie hat einen fest definierten Bereich. Dies ist die Funktion, die gespeicherte Prozedur oder der Batch, in der bzw. dem sie deklariert ist.

  Innerhalb dieses Bereichs kann eine table-Variable wie eine reguläre Tabelle verwendet werden. Sie kann überall angewendet werden, wo eine Tabelle oder ein Tabellenausdruck in SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen verwendet wird. table kann jedoch nicht in der folgenden Anweisung verwendet werden:

  SELECT select_list INTO table_variable;
  

  Für table-Variablen wird automatisch am Ende der Funktion, der gespeicherten Prozedur oder des Batches, in der bzw. dem sie definiert sind, ein Cleanup ausgeführt.

• In gespeicherten Prozeduren verwendete table-Variablen verursachen weniger Neukompilierungen der gespeicherten Prozeduren als temporäre Tabellen, sofern keine kostenbasierten Optionen vorhanden sind, die sich auf die Leistung auswirken.

• Transaktionen, an denen table-Variablen beteiligt sind, dauern nur so lange wie die Aktualisierung der table-Variablen. Daher sind für table-Variablen weniger Sperr- und Protokollierungsressourcen erforderlich.

Einschränkungen

table-Variablen werden nicht im kostenbasierten Folgerungsmodell des SQL Server-Optimierers unterstützt. Daher sollten sie nicht verwendet werden, wenn kostenbasierte Optionen erforderlich sind, um einen effizienten Abfrageplan zu realisieren. Wenn kostenbasierte Optionen erforderlich sind, werden vorzugsweise temporäre Tabellen verwendet. Dazu zählen normalerweise Abfragen mit Joins, Parallelitätsentscheidungen und Optionen für die Indexauswahl.

Abfragen, die table-Variablen ändern, generieren keine Pläne für die parallele Abfrageausführung. Die Leistung kann beeinträchtigt sein, wenn sehr große table-Variablen oder table-Variablen in komplexen Abfragen geändert werden. Erwägen Sie in vergleichbaren Situationen stattdessen die Verwendung temporärer Tabellen. Weitere Informationen finden Sie unter CREATE TABLE (Transact-SQL). Abfragen, die table-Variablen lesen, ohne sie zu ändern, können weiterhin parallelisiert werden.

Die explizite Erstellung von Indizes für table-Variablen ist nicht möglich, und zudem werden für table-Variablen keine Statistiken geführt. In einigen Fällen kann die Leistung möglicherweise verbessert werden, indem stattdessen temporäre Tabellen verwendet werden, die Indizes und Statistiken unterstützen. Weitere Informationen zu temporären Tabellen finden Sie unter CREATE TABLE (Transact-SQL).

CHECK-Einschränkungen, DEFAULT-Werte und berechnete Spalten in der table-Typdeklaration können keine benutzerdefinierten Funktionen aufrufen.

Zuweisungsvorgänge zwischen table-Variablen werden nicht unterstützt.

Transaktionsrollbacks wirken sich nicht auf table-Variablen aus, da diese Variablen einen beschränkten Bereich haben und kein Teil der permanenten Datenbank sind.

Beispiele

A. Deklarieren einer Variablen vom Typ "table"

Im folgenden Beispiel wird eine table-Variable erstellt, die die in der OUTPUT-Klausel der UPDATE-Anweisung angegebenen Werte speichert. Es folgen zwei SELECT-Anweisungen, die die Werte in @MyTableVar und die Ergebnisse des Aktualisierungsvorgangs in der Employee-Tabelle zurückgeben. Beachten Sie, dass sich die Ergebnisse in der INSERTED.ModifiedDate-Spalte von den Werten in der ModifiedDate-Spalte in der Employee -Tabelle unterscheiden. Der Grund dafür ist, dass der AFTER UPDATE-Trigger, der den Wert von ModifiedDate auf das aktuelle Datum aktualisiert, in der Employee-Tabelle definiert wird. Die von OUTPUT zurückgegebenen Spalten spiegeln jedoch die Daten vor dem Auslösen von Triggern wider. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

B. Erstellen einer Inline-Tabellenwertfunktion

Das folgende Beispiel gibt eine Inline-Tabellenwertfunktion zurück. Die Funktion gibt für jedes Produkt, das an die Filiale verkauft wurde, drei Spalten zurück: ProductID, Name und das Aggregat der gesamten Verkäufe des Jahrs als YTD Total (nach Filiale sortiert).

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Rufen Sie die Funktion mit dieser Abfrage auf.

SELECT * FROM Sales.ufn_SalesByStore (602);

Siehe auch

Verweis

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (Transact-SQL)

DECLARE @local_variable (Transact-SQL)

Abfragehinweise (Transact-SQL)

Konzepte

Benutzerdefinierte Tabellenwertfunktionen

Tabellenwertparameter (Datenbankmodul)

Verwenden des USE PLAN-Abfragehinweises