table (Transact-SQL)
Si applica a: 
SQL Server Database SQL di Azure Istanza gestita di SQL di Azure
table è un tipo di dati speciale usato per archiviare un set di risultati per l'elaborazione in un secondo momento. 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. È possibile dichiarare funzioni e variabili di tipo table. Le variabili di tipo table possono essere usate in funzioni, stored procedure e batch. Per dichiarare variabili di tipo table, usare DECLARE @local_variable.
Convenzioni relative alla sintassi Transact-SQL
Sintassi
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 )
}
Argomenti
table_type_definition
Stesso subset di informazioni utilizzato per definire una tabella in CREATE TABLE. La dichiarazione di tabella include definizioni di colonna, nomi, tipi di dati e vincoli. Gli unici tipi di vincoli consentiti sono PRIMARY KEY, UNIQUE KEY e NULL.
Per altre informazioni sulla sintassi, vedere CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) e DECLARE @local_variable (Transact-SQL).
collation_definition
Regole di confronto della colonna costituita da impostazioni locali di Microsoft Windows e uno stile di confronto, impostazioni locali di Windows e notazione binaria o regole di confronto di Microsoft SQL Server. Se non si specifica collation_definition, la colonna eredita le regole di confronto del database corrente. 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.
Osservazioni:
Nella clausola FROM di un batch, alle variabili di tipo table viene fatto riferimento in base al nome, come illustrato nell'esempio seguente:
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:
SELECT EmployeeID,
DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
ON m.EmployeeID = Employee.EmployeeID
AND m.DepartmentID = Employee.DepartmentID;
Le variabili di tabella offrono i vantaggi seguenti rispetto alle tabelle temporanee per le query su scala ridotta con piani di query che non cambiano e quando i problemi di ricompilazione sono dominanti:
Le variabili di tipo table funzionano in modo analogo alle variabili locali. Queste variabili hanno un ambito ben definito, Questa variabile può essere usata nella funzione, nella stored procedure o nel batch in cui è dichiarata.
All'interno del proprio ambito, le variabili table possono essere usate come normali tabelle. in tutti i casi in cui è possibile utilizzare una tabella o espressione di tabella in istruzioni SELECT, INSERT, UPDATE e DELETE. Non è tuttavia possibile usare table nell'istruzione seguente:
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.
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.
Le variabili di tabella sono completamente isolate per il batch che le crea in modo che non sia necessario eseguire una nuova risoluzione quando viene eseguita un'istruzione CREATE o ALTER, che può verificarsi con una tabella temporanea. Per le tabelle temporanee è necessaria una nuova risoluzione, in modo che la tabella possa fare riferimento a una stored procedure nidificata. Le variabili di tabella evitano completamente questo passaggio, in modo che le stored procedure possano usare il piano già compilato, salvando così le risorse per elaborare la stored procedure.
La durata delle transazioni che includono variabili di tipo table corrisponde solo alla durata dell'aggiornamento della variabile di tipo table. Di conseguenza, le variabili di tabella richiedono un minor numero di risorse di blocco e registrazione.
Limitazioni e restrizioni
Le variabili di tabella non dispongono di statistiche di distribuzione. Non attivano ricompilazioni. In molti casi, Optimizer compila un piano di query presupponendo che la variabile di tabella non contenga righe. 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). In tal caso, le tabelle temporanee potrebbero rappresentare una soluzione migliore. Per le query che uniscono la variabile di tabella con altre tabelle, usare l'hint RECOMPILE, che fa sì che l'utilità di ottimizzazione usi la cardinalità corretta per la variabile di tabella.
Le variabili di tipo table non sono supportate nel modello di ragionamento basato sui costi dell'utilità di ottimizzazione di SQL Server. È quindi consigliabile non usarle quando sono necessarie scelte basate sui costi per ottenere un piano di query efficiente. È preferibile utilizzare le tabelle temporanee quando sono necessarie scelte basate sui costi, Tale piano include in genere query con join, decisioni di parallelismo e scelte di selezione degli indici.
Per le query che modificano le variabili di tipo table non vengono generati piani di esecuzione di query parallele. La modifica di variabili di tipo table di grandi dimensioni o di variabili di tipo table in query complesse può influire sulle prestazioni. Nei casi in cui le variabili di tipo table vengono modificate, valutare la possibilità di usare invece tabelle temporanee. Per altre informazioni, vedere CREATE TABLE (Transact-SQL). Le query che leggono le variabili di tipo table senza modificarle possono comunque essere eseguite in parallelo.
Importante
Il livello di compatibilità del database 150 migliora le prestazioni delle variabili di tabella grazie all'introduzione della compilazione posticipata delle variabili di tabella. Per altre informazioni, vedere Compilazione posticipata delle variabili di tabella.
Non è possibile creare indici in modo esplicito su variabili di tipo table e per le variabili di tipo table non vengono mantenute statistiche. A partire da SQL Server 2014 (12.x), è stata introdotta una nuova sintassi che consente di creare determinati tipi di indice inline con la definizione della tabella. Usando questa nuova sintassi, è possibile creare indici su variabili tabella come parte della definizione della tabella. In alcuni casi, è possibile ottenere un miglioramento delle prestazioni usando tabelle temporanee, che offrono statistiche e supporto completo per l'indice. Per altre informazioni sulle tabelle temporanee e la creazione di indici inline, vedere 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. L'operazione di assegnazione tra variabili di tipo table non è supportata. 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. Le variabili di tabella non possono essere modificate dopo la creazione.
Le variabili di tabella non possono essere usate come destinazione della clausola in un'istruzione INTO SELECT ... INTO .
Non è possibile usare l'istruzione EXEC o la sp_executesql stored procedure per eseguire una query dinamica di SQL Server che fa riferimento a una variabile di tabella, se la variabile di tabella è stata creata all'esterno dell'istruzione EXEC o della sp_executesql stored procedure. Poiché è possibile fare riferimento alle variabili di tabella solo nell'ambito locale, un'istruzione EXEC e una sp_executesql stored procedure non rientrano nell'ambito della variabile di tabella. Tuttavia, è possibile creare la variabile di tabella ed eseguire tutte le elaborazioni all'interno dell'istruzione EXEC o della sp_executesql stored procedure perché l'ambito locale delle variabili di tabella si trova nell'istruzione EXEC o nella sp_executesql stored procedure.
Una variabile di tabella non è una struttura di sola memoria. Poiché una variabile di tabella potrebbe contenere più dati di quanto possa essere inserita in memoria, deve avere una posizione sul disco per archiviare i dati. Le variabili di tabella vengono create nel tempdb database in modo simile alle tabelle temporanee. Se la memoria è disponibile, le variabili di tabella e le tabelle temporanee vengono create ed elaborate durante la memoria (cache dei dati).
Variabili di tabella e tabelle temporanee
La scelta tra variabili di tabella e tabelle temporanee dipende da questi fattori:
- Numero di righe inserite nella tabella.
- Numero di ricompilazione da cui viene salvata la query.
- Tipo di query e la relativa dipendenza da indici e statistiche per le prestazioni.
In alcune situazioni, l'interruzione di una stored procedure con tabelle temporanee in stored procedure più piccole in modo che la ricompilazione venga eseguita su unità più piccole è utile.
In generale, è possibile usare le variabili di tabella quando possibile, tranne quando è presente un volume significativo di dati e si verifica un uso ripetuto della tabella. In tal caso, è possibile creare indici nella tabella temporanea per migliorare le prestazioni delle query. Tuttavia, ogni scenario può essere diverso. Microsoft consiglia di verificare se le variabili di tabella sono più utili rispetto alle tabelle temporanee per una determinata query o stored procedure.
Esempi
R. Dichiarare una variabile di tipo table
Nell'esempio seguente viene creata una variabile di tabella che archivia i valori specificati nella clausola OUTPUT dell'istruzione UPDATE. Seguono due SELECT istruzioni, che restituiscono i valori in @MyTableVar e i risultati dell'operazione di aggiornamento nella Employee tabella. I risultati nella colonna INSERTED.ModifiedDate sono diversi rispetto ai valori nella colonna ModifiedDate della tabella Employee. Questa differenza è causata dalla definizione nella tabella Employee del trigger AFTER UPDATE, che aggiorna il valore di ModifiedDate alla data corrente. Le colonne restituite da OUTPUT, tuttavia, riflettono i dati prima dell'attivazione dei trigger. Per altre informazioni, vedere Clausola OUTPUT (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. Creare una funzione inline con valori di tabella
Nell'esempio seguente viene restituita una funzione inline con valori di tabella. 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.
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
Per richiamare la funzione, eseguire la query seguente.
SELECT * FROM Sales.ufn_SalesByStore (602);