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

SI APPLICA A: sìSQL Server sìDatabase SQL di Azure noAzure SQL Data Warehouse noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

È un tipo di dati speciale usato per archiviare un set di risultati per elaborazioni successive.Is a special data type used to store a result set for processing at a later time. Il tipo table viene principalmente usato per l'archiviazione temporanea di un set di righe restituito come set di risultati di una funzione con valori di tabella.table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. È possibile dichiarare funzioni e variabili di tipo table.Functions and variables can be declared to be of type table. Le variabili di tipo table possono essere usate in funzioni, stored procedure e batch.table variables can be used in functions, stored procedures, and batches. Per dichiarare variabili di tipo table, usare DECLARE @local_variable.To declare variables of type table, use DECLARE @local_variable.

Si applica a: SQL ServerSQL Server (da SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017), Database SQL di AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Database SQL di AzureAzure SQL Database.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintassiSyntax

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

ArgomentiArguments

table_type_definitiontable_type_definition
Stesso subset di informazioni utilizzate per definire una tabella nell'istruzione CREATE TABLE.Is the same subset of information that is used to define a table in CREATE TABLE. La dichiarazione di tabella include definizioni di colonna, nomi, tipi di dati e vincoli.The table declaration includes column definitions, names, data types, and constraints. Gli unici tipi di vincoli consentiti sono PRIMARY KEY, UNIQUE KEY e NULL.The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
Per altre informazioni sulla sintassi, vedere CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) e 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
Regole di confronto della colonna costituite da impostazioni locali di MicrosoftMicrosoft Windows e stile di confronto, impostazioni locali di Windows e notazione binaria oppure regole di confronto di MicrosoftMicrosoft SQL ServerSQL Server.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. Se non si specifica collation_definition, la colonna eredita le regole di confronto del database corrente.If collation_definition isn't specified, the column inherits the collation of the current database. Se invece viene specificata come tipo CLR (Common Language Runtime) definito dall'utente, la colonna eredita le regole di confronto del tipo definito dall'utente.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.

RemarksRemarks

Nella clausola FROM di un batch, alle variabili di tipo table viene fatto riferimento in base al nome, come illustrato nell'esempio seguente:table Reference variables by name in a batch's FROM clause, as shown the following example:

SELECT Employee_ID, Department_ID FROM @MyTableVar;  

All'esterno di una clausola FROM, è necessario fare riferimento alle variabili di tipo table tramite un alias, come illustrato nell'esempio seguente: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);  

Per query in scala ridotta con piani di query che non vengono modificati e quando la ricompilazione è una preoccupazione dominante, le variabili di tipo table offrono i vantaggi seguenti:table variables provide the following benefits for small-scale queries that have query plans that don't change and when recompilation concerns are dominant:

  • Le variabili di tipo table funzionano in modo analogo alle variabili locali.A table variable behaves like a local variable. Queste variabili hanno un ambito ben definito,It has a well-defined scope. La variabile corrisponde alla funzione, alla stored procedure o al batch in cui è dichiarata.This variable is the function, stored procedure, or batch that it's declared in.
    All'interno del proprio ambito, le variabili table possono essere usate come normali tabelle.Within its scope, a table variable can be used like a regular table. in tutti i casi in cui è possibile utilizzare una tabella o espressione di tabella in istruzioni SELECT, INSERT, UPDATE e DELETE.It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. Non è tuttavia possibile usare table nell'istruzione seguente:However, table can't be used in the following statement:
SELECT select_list INTO table_variable;

La pulizia delle variabili di tipo table viene eseguita automaticamente alla fine della funzione, della stored procedure o del batch in cui sono definite.table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they're defined.

  • Quando si usano variabili di tipo table nelle stored procedure, il numero di ricompilazioni delle stored procedure risulta minore rispetto a quando vengono usate tabelle temporanee, in assenza di scelte basate sui costi che influiscono sulle prestazioni.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.
  • La durata delle transazioni che includono variabili di tipo table corrisponde solo alla durata dell'aggiornamento della variabile di tipo table.Transactions involving table variables last only for the duration of an update on the table variable. Le variabili di tipo table richiedono quindi una minore quantità di risorse di blocco e registrazione.As such, table variables require less locking and logging resources.

Limitazioni e restrizioniLimitations and restrictions

Le variabili di tipo table non includono statistiche di distribuzioneTable variables don't have distribution statistics. e non attivano ricompilazioni.They won't trigger recompiles. In molti casi, l'utilità di ottimizzazione creerà un piano di query basandosi sul presupposto che la variabile di tabella non contenga righe.In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. Per questo motivo, è necessario prestare attenzione in caso di utilizzo di una variabile di tabella se si prevede un numero elevato di righe (maggiore di 100).For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). In tal caso, le tabelle temporanee potrebbero rappresentare una soluzione migliore.Temp tables may be a better solution in this case. Per le query che uniscono in join la variabile di tabella con altre tabelle, usare l'hint RECOMPILE, con cui l'utilità di ottimizzazione userà la cardinalità corretta per la variabile di tabella.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.

Le variabili di tipo table non sono supportate nel modello di ragionamento basato sui costi dell'utilità di ottimizzazione di SQL ServerSQL Server.table variables aren't supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. È quindi consigliabile non usarle quando sono necessarie scelte basate sui costi per ottenere un piano di query efficiente.As such, they shouldn't be used when cost-based choices are required to achieve an efficient query plan. È preferibile utilizzare le tabelle temporanee quando sono necessarie scelte basate sui costi,Temporary tables are preferred when cost-based choices are required. Tale piano include in genere query con join, decisioni di parallelismo e scelte di selezione degli indici.This plan typically includes queries with joins, parallelism decisions, and index selection choices.

Per le query che modificano le variabili di tipo table non vengono generati piani di esecuzione di query parallele.Queries that modify table variables don't generate parallel query execution plans. La modifica di variabili di tipo table di grandi dimensioni o di variabili di tipo table in query complesse può influire sulle prestazioni.Performance can be affected when large table variables, or table variables in complex queries, are modified. Nei casi in cui le variabili di tipo table vengono modificate, valutare la possibilità di usare invece tabelle temporanee.Consider using temporary tables instead in situations where table variables are modified. Per altre informazioni, vedere CREATE TABLE (Transact-SQL).For more information, see CREATE TABLE (Transact-SQL). Le query che leggono le variabili di tipo table senza modificarle possono comunque essere eseguite in parallelo.Queries that read table variables without modifying them can still be parallelized.

Non è possibile creare indici in modo esplicito su variabili di tipo table e per le variabili di tipo table non vengono mantenute statistiche.Indexes can't be created explicitly on table variables, and no statistics are kept on table variables. A partire da SQL Server 2014 (12.x)SQL Server 2014 (12.x), è stata introdotta una nuova sintassi che consente di creare determinati tipi di indice inline con la definizione della tabella.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. Usando questa nuova sintassi, è possibile creare indici su variabili tabella come parte della definizione della tabella.Using this new syntax, you can create indexes on table variables as part of the table definition. In alcuni casi, è possibile ottenere un miglioramento delle prestazioni usando tabelle temporanee, che offrono statistiche e supporto completo per l'indice.In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. Per altre informazioni sulle tabelle temporanee e la creazione di indici inline, vedere CREATE TABLE (Transact-SQL).For more information about temporary tables and inline index creation, see CREATE TABLE (Transact-SQL).

I vincoli CHECK, i valori DEFAULT e le colonne calcolate nella dichiarazione del tipo table non possono chiamare funzioni definite dall'utente.CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions.

L'operazione di assegnazione tra variabili di tipo table non è supportata.Assignment operation between table variables isn't supported.

Dato che hanno ambito limitato e non fanno parte del database permanente, le variabili di tipo table non sono interessate dalle operazioni di rollback di transazioni.Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them.

Le variabili di tabella non possono essere modificate dopo la creazione.Table variables can't be altered after creation.

Compilazione posticipata delle variabili di tabellaTable variable deferred compilation

La compilazione posticipata delle variabili di tabella migliora la qualità del piano e le prestazioni generali per le query che fanno riferimento a variabili di tabella.Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. Durante l'ottimizzazione e la compilazione iniziale del piano, questa funzionalità propagherà le stime della cardinalità basate sui conteggi effettivi delle righe di variabili di tabella.During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. Queste informazioni esatte sui conteggi delle righe verranno quindi usate per ottimizzare le operazioni del piano a valle.This exact row count information will then be used for optimizing downstream plan operations.

Nota

La compilazione posticipata delle variabili di tabella è una funzionalità di anteprima pubblica in Database SQL di AzureAzure SQL Database e SQL Server 2019SQL Server 2019.Table variable deferred compilation is a public preview feature in Database SQL di AzureAzure SQL Database and SQL Server 2019SQL Server 2019.

Con la compilazione posticipata delle variabili di tabella, la compilazione di un'istruzione che fa riferimento a una variabile di tabella viene posticipata fino alla prima esecuzione effettiva dell'istruzione.With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. Questo comportamento della compilazione posticipata è identico a quello delle tabelle temporanee.This deferred compilation behavior is identical to the behavior of temporary tables. Questo cambiamento determina l'uso della cardinalità effettiva invece dell'ipotesi originale di una sola riga.This change results in the use of actual cardinality instead of the original one-row guess.

Per abilitare l'anteprima pubblica della compilazione posticipata delle variabili di tabella, abilitare il livello di compatibilità del database 150 per il database a cui si è connessi quando si esegue la query.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.

La compilazione posticipata delle variabili di tabella non modifica altre caratteristiche delle variabili di tabella.Table variable deferred compilation doesn't change any other characteristics of table variables. Ad esempio, questa funzionalità non aggiunge statistiche di colonna alle variabili di tabella.For example, this feature doesn't add column statistics to table variables.

La compilazione posticipata delle variabili di tabella non aumenta la frequenza di ricompilazione.Table variable deferred compilation doesn't increase recompilation frequency. Piuttosto, sposta la posizione di esecuzione della compilazione iniziale.Rather, it shifts where the initial compilation occurs. Il piano memorizzato nella cache risultante viene generato in base al conteggio delle righe delle variabili di tabella della compilazione posticipata iniziale.The resulting cached plan generates based on the initial deferred compilation table variable row count. Il piano memorizzato nella cache viene riusato da query consecutive,The cached plan is reused by consecutive queries. fino a quando non viene rimosso o ricompilato.It's reused until the plan is evicted or recompiled.

Il conteggio delle righe delle variabili di tabella usato per la compilazione del piano iniziale rappresenta un valore tipico e potrebbe essere diverso da un'ipotesi di conteggio di righe fisso.Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. Se è diverso, è un vantaggio per le operazioni a valle.If it's different, downstream operations will benefit. Se il conteggio delle righe delle variabili di tabella varia notevolmente tra le esecuzioni, questa funzionalità potrebbe non migliorare le prestazioni.Performance may not be improved by this feature if the table variable row count varies significantly across executions.

Disabilitazione della compilazione posticipata delle variabili di tabella senza modificare il livello di compatibilitàDisabling table variable deferred compilation without changing the compatibility level

Disabilitare la compilazione posticipata delle variabili di tabella nell'ambito del database o dell'istruzione mantenendo comunque un livello di compatibilità del database 150 o superiore.Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. Per disabilitare la compilazione posticipata delle variabili di tabella per tutte le esecuzioni di query originate dal database, eseguire questo esempio nel contesto del database applicabile: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;

Per riabilitare la compilazione posticipata delle variabili di tabella per tutte le esecuzioni di query originate dal database, eseguire questo esempio nel contesto del database applicabile: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;

È anche possibile disabilitare la compilazione posticipata delle variabili di tabella per una query specifica assegnando DISABLE_DEFERRED_COMPILATION_TV come hint per la query USE HINT.You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. Esempio: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'));

EsempiExamples

A.A. Dichiarazione di una variabile di tipo tableDeclaring a variable of type table

Nell'esempio seguente viene creata una variabile di tipo table in cui vengono archiviati i valori specificati nella clausola OUTPUT dell'istruzione UPDATE.The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Questa variabile è seguita da due istruzioni SELECT che restituiscono i valori in @MyTableVar e i risultati dell'operazione di aggiornamento nella tabella Employee.Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. I risultati nella colonna INSERTED.ModifiedDate sono diversi rispetto ai valori nella colonna ModifiedDate della tabella Employee.Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. Questa differenza è causata dalla definizione nella tabella Employee del trigger AFTER UPDATE, che aggiorna il valore di ModifiedDate alla data corrente.This difference is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. Le colonne restituite da OUTPUT, tuttavia, riflettono i dati prima dell'attivazione dei trigger.However, the columns returned from OUTPUT reflect the data before triggers are fired. Per altre informazioni, vedere Clausola OUTPUT (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. Creazione di una funzione inline con valori di tabellaCreating an inline table-valued function

Nell'esempio seguente viene restituita una funzione inline con valori di tabella.The following example returns an inline table-valued function. L'esempio restituisce tre colonne ProductID, Name e l'aggregazione dei totali da inizio anno per negozio, come YTD Total per ogni prodotto venduto al negozio.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  

Per richiamare la funzione, eseguire la query seguente.To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

Vedere ancheSee also

COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
Funzioni definite dall'utenteUser-Defined Functions
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
Usare parametri con valori di tabella (Motore di database)Use Table-Valued Parameters (Database Engine)
Hint di query (Transact-SQL)Query Hints (Transact-SQL)