table (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

table ist ein spezieller Datentyp, der zum Speichern eines Resultsets für die Verarbeitung zu einem späteren Zeitpunkt verwendet wird. table wird hauptsächlich für die temporäre Speicherung eines Zeilensatzes verwendet, der als Resultset einer Tabellenwertfunktion zurückgegeben wird. Für Funktionen und Variablen kann der Typ table angegeben werden. table-Variablen können in Funktionen, gespeicherten Prozeduren und Batches verwendet werden. Verwenden Sie zum Deklarieren von Variablen des table-Typs 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 der 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.

Bemerkungen

table verweist anhand des Namens in der FROM-Klausel eines Batches auf Variablen, wie im folgenden Beispiel gezeigt:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

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

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

table-Variablen bieten gegenüber temporären Tabellen die folgenden Vorteile für Abfragen mit kleinerem Umfang, die über Abfragepläne verfügen, die sich nicht ändern (gilt auch für Szenarios mit vorwiegend vorhandenen Neukompilierungsaspekten):

• Eine table-Variable verhält sich wie eine lokale Variable. Sie hat einen fest definierten Bereich. Diese Variable kann in der Funktion, der gespeicherten Prozedur oder dem Batch verwendet werden, 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 bei Verwendung temporärer Tabellen, wenn keine kostenbasierten Optionen vorhanden sind, welche die Leistung beeinflussen.

  Tabellenvariablen sind vollständig auf den Batch beschränkt, der sie erstellt, sodass keine Neuauflösung erfolgen muss, wenn eine CREATE- oder ALTER-Anweisung verwendet wird, was bei einer temporären Tabelle der Fall sein kann. Temporäre Tabellen benötigen diese Neuauflösung, damit von einer geschachtelten gespeicherten Prozedur auf die Tabelle verwiesen werden kann. Tabellenvariablen vermeiden diesen Schritt vollständig, sodass gespeicherte Prozeduren bereits kompilierte Pläne verwenden können, wodurch Ressourcen für die Verarbeitung der gespeicherten Prozedur eingespart werden können.

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

Einschränkungen

table-Variablen haben keine Verteilungsstatistiken. Sie lösen keine Neukompilierungen aus. Daher erstellt der Optimierer in vielen Fällen einen Abfrageplan unter der Annahme, dass die table-Variable keine Zeilen enthält. Aus diesem Grund sollten Sie Tabellenvariablen mit Vorsicht verwenden, wenn Sie von einer großen Anzahl von Zeilen (mehr als 100) ausgehen. Für solche Fälle sind temporäre Tabellen möglicherweise die bessere Lösung. Verwenden Sie bei Abfragen, die einen Join der Tabelle mit anderen Tabellen ausführen, auch den RECOMPILE-Hinweis. Dieser führt dazu, dass der Optimierer die korrekte Kardinalität für die table-Variable verwendet.

table-Variablen werden im kostenbasierten Ansatzmodell des SQL Server-Optimierers nicht unterstützt. Daher sollten sie nicht verwendet werden, wenn kostenbasierte Optionen erforderlich sind, um einen effizienten Abfrageplan zu erzielen. Temporäre Tabellen werden bevorzugt, wenn kostenbasierte Optionen erforderlich sind. Dieser Plan schließt in der Regel Abfragen mit Joins, Parallelverarbeitungsentscheidungen und Indexauswahloptionen ein.

Abfragen, die table-Variablen ändern, generieren keine Pläne für die parallele Abfrageausführung. Die Leistung kann beeinträchtigt sein, wenn große table-Variablen oder table-Variablen in komplexen Abfragen geändert werden. Erwägen Sie daher in Situationen, in denen table-Variablen geändert werden, die Verwendung von temporären 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, zudem werden für table-Variablen keine Statistiken geführt. Mit SQL Server 2014 (12.x) wurde eine neue Syntax eingeführt, die es erlaubt, bestimmte Indextypen inline mit der Tabellendefinition zu erstellen. Mit dieser neuen Syntax können Sie Indizes für table-Variablen als Teil der Tabellendefinition erstellen. In einigen Fällen kann die Leistung verbessert werden, indem stattdessen temporäre Tabellen verwendet werden, die eine vollständige Unterstützung für Indizes und Statistiken bieten. Weitere Informationen zu temporären Tabellen und der Inlineerstellung von Indizes 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 eingeschränkten Bereich haben und kein Teil der permanenten Datenbank sind. „table“-Variablen können nach ihrer Erstellung nicht mehr geändert werden.

Tabellenvariablen können nicht als Ziel der INTO Klausel in einer SELECT ... INTO Anweisung verwendet werden.

Sie können die EXEC-Anweisung oder die gespeicherte Prozedur sp_executesql nicht verwenden, um eine dynamische SQL Server-Abfrage auszuführen, die auf eine Tabellenvariable verweist, wenn die Tabellenvariable außerhalb der EXEC-Anweisung oder der gespeicherten Prozedur sp_executesql erstellt wurde. Da Tabellenvariablen nur im lokalen Bereich referenziert werden können, würden sich eine EXEC-Anweisung und eine gespeicherte Prozedur sp_executesql außerhalb des Bereichs der Tabellenvariablen befinden. Sie können jedoch die Tabellenvariable erstellen und die gesamte Verarbeitung innerhalb der EXEC-Anweisung oder der gespeicherten Prozedur sp_executesql ausführen, da sich dann der lokale Bereich der Tabellenvariablen in der EXEC-Anweisung oder der gespeicherten Prozedur sp_executesql befindet.

Eine Tabellenvariable ist keine reine Arbeitsspeicherstruktur. Da eine Tabellenvariable möglicherweise mehr Daten enthält, als in den Arbeitsspeicher passen kann, muss sie einen Platz auf dem Datenträger haben, um Daten zu speichern. Tabellenvariablen werden ähnlich wie temporäre Tabellen in der tempdb-Datenbank erstellt. Wenn Arbeitsspeicher verfügbar ist, werden sowohl Tabellenvariablen als auch temporäre Tabellen im Arbeitsspeicher (Datencache) erstellt und verarbeitet.

Tabellenvariablen und temporäre Tabellen im Vergleich

Die Wahl zwischen Tabellenvariablen und temporären Tabellen hängt von den folgenden Faktoren ab:

• Der Anzahl der Zeilen, die in die Tabelle eingefügt werden.
• Der Anzahl der Neukompilierungen, aus denen die Abfrage gespeichert wird.
• Der Typ der Abfragen und deren Abhängigkeit von Indizes und Statistiken für die Leistung.

In einigen Situationen ist es hilfreich, eine gespeicherte Prozedur mit temporären Tabellen in kleinere gespeicherte Prozeduren aufzuteilen, sodass die Neukompilierung auf kleineren Einheiten erfolgt.

Im Allgemeinen verwenden Sie nach Möglichkeit Tabellenvariablen, es sei denn, es handelt sich um ein großes Datenvolumen und die Tabelle wird wiederholt verwendet. In diesem Fall können Sie Indizes für die temporäre Tabelle erstellen, um die Abfrageleistung zu erhöhen. Die Szenarien können jedoch unterschiedlich sein. Microsoft empfiehlt, zu testen, ob Tabellenvariablen für eine bestimmte Abfrage oder gespeicherte Prozedur hilfreicher sind als temporäre Tabellen.

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 Updatevorgangs in der Employee-Tabelle zurückgeben. Die Ergebnisse in der INSERTED.ModifiedDate-Spalte weichen von den Werten in der ModifiedDate-Spalte in der Employee-Tabelle ab. Der Grund für die Abweichung ist, dass der AFTER UPDATE-Trigger, der den Wert von ModifiedDate auf das aktuelle Datum aktualisiert, in der Employee-Tabelle definiert ist. Die von OUTPUT zurückgegebenen Spalten spiegeln jedoch die Daten wider, bevor Trigger ausgelöst werden. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME
);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
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.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
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 drei Spalten ProductID, Name und das Aggregat der gesamten Verkäufe des Jahres (nach Filiale sortiert) als YTD Total für jedes Produkt zurück, das an die Filiale verkauft wurde.

USE AdventureWorks2022;
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
    INNER JOIN Sales.SalesOrderDetail AS SD
        ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH
        ON SH.SalesOrderID = SD.SalesOrderID
    INNER 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);

Weitere Informationen

• COLLATE (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• Benutzerdefinierte Funktionen
• CREATE TABLE (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Verwenden von Tabellenwertparameter (Datenbank-Engine)
• Abfragehinweise (Transact-SQL)