table (Transact-SQL)

Tipo di dati speciale utilizzabile per archiviare un set di risultati per l'elaborazione successiva. table viene utilizzato principalmente come spazio di 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 sono utilizzabili in funzioni, stored procedure e batch. Per dichiarare variabili di tipo table, utilizzare DECLARE @local_variable.

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 un'impostazione locale di Microsoft Windows e uno stile di confronto, un'impostazione locale 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.

Procedure consigliate

Non utilizzare variabili di tabella per archiviare grandi quantità di dati (più di 100 righe). Le scelte del piano potrebbero non risultare ottimali o stabili in presenza di una variabile di tabella contenente una grande quantità di dati. Considerare la possibilità di riscrivere tali query in modo che vengano utilizzate tabelle temporanee o l'hint per la query USE PLAN al fine di assicurare che Query Optimizer utilizzi un piano di query esistente appropriato per lo scenario specifico.

Osservazioni generali

È possibile fare riferimento alle variabili 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 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 table forniscono i seguenti vantaggi per le query su scala limitata con piani di query che rimangono invariati e quando i problemi di ricompilazione assumono un aspetto predominante:

• Le variabili 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 table vengono cancellate automaticamente alla fine della funzione, della stored procedure o del batch in cui sono definite.

• Quando si utilizzano variabili table in stored procedure, il numero di ricompilazioni delle stored procedure risulta inferiore rispetto al numero eseguito con tabelle temporanee, quando non sono presenti scelte basate sui costi che influiscono sulle prestazioni.

• La durata delle transazioni che includono variabili table corrisponde solo alla durata dell'aggiornamento della variabile table. Le variabili table richiedono pertanto un numero di risorse di blocco e di registrazione minore.

Limitazioni e restrizioni

Le variabili table non sono supportate nel modello di valutazione basato sui costi dell'utilità di ottimizzazione di SQL Server. Non dovranno quindi essere utilizzate quando per ottenere un piano di query efficiente sono richieste scelte basate sui costi. Qualora siano necessarie scelte basate sui costi, è preferibile utilizzare tabelle temporanee. Tali scelte includono solitamente query con join, decisioni relative al parallelismo e opzioni di selezione dell'indice.

Per le query che modificano le variabili table non vengono generati piani di esecuzione parallela. 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 table senza modificarle possono comunque essere eseguite in parallelo.

Non è possibile creare indici in modo esplicito su variabili table e per le variabili 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 tramite i vincoli CHECK, i valori DEFAULT e le colonne calcolate nella dichiarazione del tipo table.

Le operazioni di assegnazione tra variabili table non sono supportate.

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

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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 con valori di tabella

Parametri con valori di tabella (Motore di database)

Utilizzo dell'hint per la query USE PLAN