table (Transact-SQL)

Articolo
09/10/2013

Tipo di dati speciale che può essere utilizzato per archiviare un set di risultati per l'elaborazione in un secondo momento. table viene principalmente utilizzato per l'archiviazione temporanea di un set di righe restituite come set di risultati di una funzione con valori di tabella. È possibile dichiarare funzioni e variabili di tipo table. Le variabili di tipo table sono utilizzabili in funzioni, stored procedure e batch. Per dichiarare variabili di tipo table, utilizzare DECLARE @local\_variable.

Icona di collegamento a un argomento Convenzioni della 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 utilizzate per definire una tabella nell'istruzione 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 ulteriori 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 composte da impostazioni locali di Microsoft Windows e uno stile di confronto, impostazioni locali di Windows e la notazione binaria oppure dalle regole di confronto di Microsoft SQL Server. Se collation_definition viene omesso, 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 generali

È possibile fare riferimento alle variabili di tipo table per nome nella clausola FROM di un batch, 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
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

Le variabili di tipo table offrono i vantaggi seguenti per le query in scala ridotta contenenti piani di query che non vengono modificati 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, corrispondente alla funzione, alla stored procedure o al batch in cui sono dichiarate.

All'interno del proprio ambito, le variabili table possono essere utilizzate come normali tabelle in tutti i casi in cui è possibile utilizzare una tabella o espressione di tabella in istruzioni SELECT, INSERT, UPDATE e DELETE. Le variabili table, tuttavia, non possono essere utilizzate nell'istruzione seguente:
```
SELECT select_list INTO table_variable;
```
Le variabili di tipo table vengono cancellate automaticamente alla fine della funzione, della stored procedure o del batch in cui sono definite.
Quando si utilizzano variabili di tipo table in stored procedure, il numero di ricompilazioni delle stored procedure risulta minore rispetto al numero eseguito con tabelle temporanee, non essendoci scelte basate sui costi che influiscono sulle prestazioni.
La durata delle transazioni che includono variabili di tipo table corrisponde solo alla durata dell'aggiornamento della variabile di tipo table. Le variabili di tipo table richiedono pertanto un numero di risorse di blocco e di registrazione minore.

Limitazioni e restrizioni

Le variabili di tipo Table non contengono statistiche di distribuzione e non generano ricompilazioni. Pertanto, in molti casi, l'utilità di ottimizzazione compila un piano di query supponendo 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. In alternativa, per le query che uniscono in join la variabile di tabella con altre tabelle, utilizzare l'hint RECOMPILE, che fa in modo che l'utilità di ottimizzazione utilizzi 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. È pertanto consigliabile non utilizzarle 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, ad esempio 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 table di dimensioni molto grandi o di variabili di table in query complesse può influire sulle prestazioni. In questi casi, valutare l'utilizzo di tabelle temporanee in alternativa. Per ulteriori informazioni, vedere CREATE TABLE (Transact-SQL). Le query che leggono le variabili di tipo table senza modificarle possono comunque essere eseguite in parallelo.

Non è possibile creare indici in modo esplicito su variabili di tipo table e per le variabili di tipo table non vengono mantenute statistiche. In alcuni casi, è possibile ottenere un miglioramento delle prestazioni utilizzando tabelle temporanee, che supportano indici e statistiche. Per ulteriori informazioni sulle tabelle temporanee, vedere CREATE TABLE (Transact-SQL).

Non è possibile chiamare funzioni definite dall'utente dai vincoli CHECK, dai valori DEFAULT e dalle colonne calcolate nella dichiarazione del tipo table.

Non sono supportate operazioni di assegnazione tra variabili di tipo table.

Inoltre, le operazioni di rollback delle transazioni non hanno alcun effetto sulle variabili di tipo table, in quanto tali variabili hanno un ambito limitato e non fanno parte del database persistente.

Le variabili di tabella non possono essere modificate dopo la creazione.

Esempi

A.Dichiarazione di una variabile di tipo table

Nell'esempio seguente viene creata una variabile di tipo table in cui vengono archiviati i valori specificati nella clausola OUTPUT dell'istruzione UPDATE. Questa variabile è seguita da due istruzioni SELECT che restituiscono i valori in @MyTableVar e i risultati dell'operazione di aggiornamento nella tabella Employee. Si noti che i risultati nella colonna INSERTED.ModifiedDate sono diversi rispetto ai valori nella colonna ModifiedDate della tabella Employee . Questo perché nella tabella Employee è stato definito il trigger AFTER UPDATE, che aggiorna il valore di ModifiedDate in base alla data corrente. Le colonne restituite da OUTPUT, tuttavia, riflettono i dati prima dell'attivazione dei trigger. Per ulteriori informazioni, vedere Clausola OUTPUT (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,
    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.Creazione di una funzione inline con valori di tabella

Nell'esempio seguente viene restituita una funzione inline con valori di tabella. Vengono restituite tre colonne ProductID, Name e il valore aggregato dei totali dell'anno in corso per negozio come YTD Total per ogni prodotto venduto al negozio.

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.

SELECT * FROM Sales.ufn_SalesByStore (602);

Vedere anche

Riferimento

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

Hint per la query (Transact-SQL)

Concetti

Funzioni definite dall'utente

Utilizzare parametri con valori di tabella (Motore di database)

Share via