table (Transact-SQL)table (Transact-SQL)

GILT FÜR: jaSQL ServerjaAzure SQL-DatenbankneinAzure SQL Data Warehouse neinParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Entspricht einem speziellen Datentyp, der zum Speichern eines Resultsets für die Verarbeitung zu einem späteren Zeitpunkt verwendet wird.Is a special data type used to store a result set for processing at a later time. table wird hauptsächlich für die temporäre Speicherung eines Zeilensatzes verwendet, der als Resultset einer Tabellenwertfunktion zurückgegeben wird.table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. Für Funktionen und Variablen kann der Typ table angegeben werden.Functions and variables can be declared to be of type table. table-Variablen können in Funktionen, gespeicherten Prozeduren und Batches verwendet werden.table variables can be used in functions, stored procedures, and batches. Verwenden Sie zum Deklarieren von Variablen des table-Typs die Anweisung DECLARE @local_variable.To declare variables of type table, use DECLARE @local_variable.

Gilt für: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 bis SQL Server 2017SQL Server 2017), Azure SQL-DatenbankAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL-DatenbankAzure SQL Database.

Themenlinksymbol Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

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 )   
     }   

ArgumenteArguments

table_type_definitiontable_type_definition
Dieselbe Teilmenge von Informationen, die zum Definieren einer Tabelle in CREATE TABLE verwendet wird.Is the same subset of information that is used to define a table in CREATE TABLE. Die Tabellendeklaration schließt Spaltendefinitionen, Namen, Datentypen und Einschränkungen ein.The table declaration includes column definitions, names, data types, and constraints. Die einzigen zulässigen Einschränkungstypen sind PRIMARY KEY, UNIQUE KEY und NULL.The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
Weitere Informationen zur Syntax finden Sie unter CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) und DECLARE @local_variable (Transact-SQL).For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local_variable (Transact-SQL).

collation_definitioncollation_definition
Die Sortierung einer Spalte, die aus einem MicrosoftMicrosoft Windows-Gebietsschema und einer Vergleichsart, einem Windows-Gebietsschema und der Binärschreibweise oder einer MicrosoftMicrosoft SQL ServerSQL Server-Sortierung besteht.Is the collation of the column that is made up of a MicrosoftMicrosoft Windows locale and a comparison style, a Windows locale, and the binary notation, or a MicrosoftMicrosoft SQL ServerSQL Server collation. Wenn collation_definition nicht angegeben ist, erbt die Spalte die Sortierung der aktuellen Datenbank.If collation_definition isn't specified, the column inherits the collation of the current database. Wenn die Spalte als CLR-benutzerdefinierter Typ (Common Language Runtime) definiert ist, erbt die Spalte die Sortierung des benutzerdefinierten Typs.Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

BemerkungenRemarks

table verweist anhand des Namens in der FROM-Klausel eines Batches auf Variablen, wie im folgenden Beispiel gezeigt:table Reference variables by name in a batch's FROM clause, as shown the following example:

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:Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

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

table-Variablen bieten 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):table variables provide the following benefits for small-scale queries that have query plans that don't change and when recompilation concerns are dominant:

  • Eine table-Variable verhält sich wie eine lokale Variable.A table variable behaves like a local variable. Sie hat einen fest definierten Bereich.It has a well-defined scope. Diese Variable ist die Funktion, die gespeicherte Prozedur oder der Batch, in der bzw. dem sie deklariert ist.This variable is the function, stored procedure, or batch that it's declared in.
    Innerhalb dieses Bereichs kann eine table-Variable wie eine reguläre Tabelle verwendet werden.Within its scope, a table variable can be used like a regular table. Sie kann überall angewendet werden, wo eine Tabelle oder ein Tabellenausdruck in SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen verwendet wird.It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. table kann jedoch nicht in der folgenden Anweisung verwendet werden:However, table can't be used in the following statement:
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.table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they're defined.

  • 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.table variables that are used in stored procedures cause fewer stored procedure recompilations than when temporary tables are used when there are no cost-based choices that affect performance.
  • Transaktionen, an denen table-Variablen beteiligt sind, dauern nur so lange wie das Update der table-Variablen.Transactions involving table variables last only for the duration of an update on the table variable. Daher sind für table-Variablen weniger Sperr- und Protokollierungsressourcen erforderlich.As such, table variables require less locking and logging resources.

EinschränkungenLimitations and restrictions

table-Variablen haben keine Verteilungsstatistiken.Table variables don't have distribution statistics. Sie lösen keine Neukompilierungen aus.They won't trigger recompiles. Daher erstellt der Optimierer in vielen Fällen einen Abfrageplan unter der Annahme, dass die „table“-Variable keine Zeilen enthält.In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. Aus diesem Grund sollten Sie Tabellenvariablen mit Vorsicht verwenden, wenn Sie von einer großen Anzahl von Zeilen (mehr als 100) ausgehen.For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Für solche Fälle sind temporäre Tabellen möglicherweise die bessere Lösung.Temp tables may be a better solution in this case. 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.For queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.

table-Variablen werden im kostenbasierten Ansatzmodell des SQL ServerSQL Server-Optimierers nicht unterstützt.table variables aren't supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. Daher sollten sie nicht verwendet werden, wenn kostenbasierte Optionen erforderlich sind, um einen effizienten Abfrageplan zu erzielen.As such, they shouldn't be used when cost-based choices are required to achieve an efficient query plan. Temporäre Tabellen werden bevorzugt, wenn kostenbasierte Optionen erforderlich sind.Temporary tables are preferred when cost-based choices are required. Dieser Plan schließt in der Regel Abfragen mit Joins, Parallelverarbeitungsentscheidungen und Indexauswahloptionen ein.This plan typically includes queries with joins, parallelism decisions, and index selection choices.

Abfragen, die table-Variablen ändern, generieren keine Pläne für die parallele Abfrageausführung.Queries that modify table variables don't generate parallel query execution plans. Die Leistung kann beeinträchtigt sein, wenn große table-Variablen oder table-Variablen in komplexen Abfragen geändert werden.Performance can be affected when large table variables, or table variables in complex queries, are modified. Erwägen Sie daher in Situationen, in denen table-Variablen geändert werden, die Verwendung von temporären Tabellen.Consider using temporary tables instead in situations where table variables are modified. Weitere Informationen finden Sie unter CREATE TABLE (Transact-SQL).For more information, see CREATE TABLE (Transact-SQL). Abfragen, die table-Variablen lesen, ohne sie zu ändern, können weiterhin parallelisiert werden.Queries that read table variables without modifying them can still be parallelized.

Die explizite Erstellung von Indizes für table-Variablen ist nicht möglich, zudem werden für table-Variablen keine Statistiken geführt.Indexes can't be created explicitly on table variables, and no statistics are kept on table variables. Mit SQL Server 2014 (12.x)SQL Server 2014 (12.x) wurde eine neue Syntax eingeführt, die es erlaubt, bestimmte Indextypen inline mit der Tabellendefinition zu erstellen.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. Mit dieser neuen Syntax können Sie Indizes für table-Variablen als Teil der Tabellendefinition erstellen.Using this new syntax, you can create indexes on table variables as part of the table definition. 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.In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. Weitere Informationen zu temporären Tabellen und der Inlineerstellung von Indizes finden Sie unter CREATE TABLE (Transact-SQL).For more information about temporary tables and inline index creation, see CREATE TABLE (Transact-SQL).

CHECK-Einschränkungen, DEFAULT-Werte und berechnete Spalten in der table-Typdeklaration können keine benutzerdefinierten Funktionen aufrufen.CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions.

Zuweisungsvorgänge zwischen table-Variablen werden nicht unterstützt.Assignment operation between table variables isn't supported.

Transaktionsrollbacks wirken sich nicht auf table-Variablen aus, da diese Variablen einen eingeschränkten Bereich haben und kein Teil der permanenten Datenbank sind.Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them.

„table“-Variablen können nach ihrer Erstellung nicht mehr geändert werden.Table variables can't be altered after creation.

Verzögerte Kompilierung von TabellenvariablenTable variable deferred compilation

Die verzögerte Kompilierung von Tabellenvariablen verbessert die Qualität des Abfrageplans und die Gesamtleistung für Abfragen mit Verweisen auf Tabellenvariablen.Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. Während der Optimierung und der ersten Kompilierung des Plans verteilt diese Funktion Kardinalitätsschätzungen, die auf tatsächlichen Tabellenvariablen-Zeilenzahlen basieren.During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. Diese Informationen zur genauen Zeilenzahl werden dann zur Optimierung der nachgelagerten Planvorgänge verwendet.This exact row count information will then be used for optimizing downstream plan operations.

Hinweis

Die verzögerte Kompilierung von Tabellenvariablen ist in Azure SQL-DatenbankAzure SQL Database und SQL Server 2019 (15.x)SQL Server 2019 (15.x) ein Feature in der öffentlichen Preview.Table variable deferred compilation is a public preview feature in Azure SQL-DatenbankAzure SQL Database and SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Bei der verzögerten Kompilierung von Tabellenvariablen wird die Kompilierung einer Anweisung, die auf eine Tabellenvariable verweist, bis zur ersten tatsächlichen Ausführung der Anweisung verzögert.With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. Dieses Verhalten der verzögerten Kompilierung ist identisch mit dem von temporären Tabellen.This deferred compilation behavior is identical to the behavior of temporary tables. Diese Änderung führt dazu, dass anstelle des ursprünglichen einreihigen Schätzwertes die tatsächliche Kardinalität verwendet wird.This change results in the use of actual cardinality instead of the original one-row guess.

Um die öffentliche Vorschau der verzögerten Kompilierung von Tabellenvariablen zu aktivieren, aktivieren Sie Datenbank-Kompatibilitätsgrad 150 für die Datenbank, mit der Sie beim Ausführen der Abfrage verbunden sind.To enable the public preview of table variable deferred compilation, enable database compatibility level 150 for the database you're connected to when the query runs.

Die verzögerte Kompilierung von Tabellenvariablen führt nicht zu Änderungen an anderen Merkmalen von Tabellenvariablen.Table variable deferred compilation doesn't change any other characteristics of table variables. Beispielsweise wird durch dieses Feature keine Spaltenstatistik zu Tabellenvariablen hinzugefügt.For example, this feature doesn't add column statistics to table variables.

Die verzögerte Kompilierung von Tabellenvariablen führt nicht zu einer häufigeren Neukompilierung.Table variable deferred compilation doesn't increase recompilation frequency. Stattdessen wird der Zeitpunkt der ersten Kompilierung verschoben.Rather, it shifts where the initial compilation occurs. Der resultierende zwischengespeicherte Plan wird basierend auf der anfänglichen Zeilenanzahl für die verzögerte Kompilierung von Tabellenvariablen generiert.The resulting cached plan generates based on the initial deferred compilation table variable row count. Der zwischengespeicherte Plan wird von nachfolgenden Abfragen wiederverwendet.The cached plan is reused by consecutive queries. Es wird solange wiederverwendet, bis der Plan entfernt oder erneut kompiliert wird.It's reused until the plan is evicted or recompiled.

Die Zeilenanzahl für Tabellenvariablen, die für die anfängliche Plankompilierung verwendet wird, stellt einen typischen Wert dar, der von einem geschätzten Festwert für die Zeilenanzahl abweichen kann.Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. Bei Abweichungen profitieren Downstreamvorgänge.If it's different, downstream operations will benefit. Weicht die Zeilenanzahl für Tabellenvariablen für alle durchgeführten Ausführungen erheblich ab, wird die Leistung durch dieses Feature möglicherweise nicht verbessert.Performance may not be improved by this feature if the table variable row count varies significantly across executions.

Deaktivieren der verzögerten Kompilierung von Tabellenvariablen ohne Änderung des KompatibilitätsgradsDisabling table variable deferred compilation without changing the compatibility level

Deaktivieren Sie die verzögerte Kompilierung von Tabellenvariablen im Datenbank- oder Anweisungsbereich, während Sie den Datenbankkompatibilitätsgrad bei 150 und höher beibehalten.Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. Um die verzögerte Kompilierung von Tabellenvariablen für alle Abfrageausführungen zu deaktivieren, die aus der Datenbank stammen, führen Sie das folgende Beispiel im Kontext der betroffenen Datenbank aus:To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

Um die verzögerte Kompilierung von Tabellenvariablen für alle Abfrageausführungen, die aus der Datenbank stammen, erneut zu aktivieren, führen Sie das folgende Beispiel im Kontext der betroffenen Datenbank aus:To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

Sie können die verzögerte Kompilierung von Tabellenvariablen auch für eine bestimmte Abfrage deaktivieren, indem Sie DISABLE_DEFERRED_COMPILATION_TV als USE HINT-Abfragehinweis zuweisen.You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. Beispiel:For example:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT  O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE   O_ORDERKEY  =   L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

BeispieleExamples

A.A. Deklarieren einer Variablen vom Typ "table"Declaring a variable of type table

Im folgenden Beispiel wird eine table-Variable erstellt, die die in der OUTPUT-Klausel der UPDATE-Anweisung angegebenen Werte speichert.The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Es folgen zwei SELECT-Anweisungen, die die Werte in @MyTableVar und die Ergebnisse des Updatevorgangs in der Employee-Tabelle zurückgeben.Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Die Ergebnisse in der INSERTED.ModifiedDate-Spalte weichen von den Werten in der ModifiedDate-Spalte in der Employee-Tabelle ab.Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. 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.This difference is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. Die von OUTPUT zurückgegebenen Spalten spiegeln jedoch die Daten wider, bevor Trigger ausgelöst werden.However, the columns returned from OUTPUT reflect the data before triggers are fired. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).For more information, see OUTPUT Clause (Transact-SQL).

USE AdventureWorks2012;  
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.B. Erstellen einer Inline-TabellenwertfunktionCreating an inline table-valued function

Das folgende Beispiel gibt eine Inline-Tabellenwertfunktion zurück.The following example returns an inline table-valued function. 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.It returns three columns ProductID, Name, and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

USE AdventureWorks2012;  
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.To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

Siehe auchSee also

COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
Benutzerdefinierte FunktionenUser-Defined Functions
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
Verwenden von Tabellenwertparametern (Datenbank-Engine)Use Table-Valued Parameters (Database Engine)
Abfragehinweise (Transact-SQL)Query Hints (Transact-SQL)