Usare i parametri Table-Valued (motore di database)

I parametri con valori di tabella vengono dichiarati utilizzando tipi di tabella definiti dall'utente. I parametri con valori di tabella consentono di inviare più righe di dati a un'istruzione o a una routine Transact-SQL, ad esempio una stored procedure o una funzione, senza creare una tabella temporanea o molti parametri.

I parametri con valori di tabella sono analoghi alle matrici di parametri in OLE DB e ODBC, ma offrono più flessibilità e maggiore integrazione con Transact-SQL. I parametri con valori di tabella hanno anche il vantaggio di poter partecipare in operazioni basate su set.

Transact-SQL passa i parametri con valori di tabella alle routine per riferimento in modo da evitare l'esecuzione di una copia dei dati di input. È possibile creare ed eseguire routine Transact-SQL con parametri con valori di tabella e chiamarle da codice Transact-SQL o client gestiti e nativi in qualsiasi linguaggio gestito.

In questo argomento:

Vantaggi

restrizioni

ParametriTable-Valued contro operazioni BULK INSERT

Esempio

Vantaggi

L'ambito di un parametro con valori di tabella è costituito dalla stored procedure, dalla funzione o dal testo Transact-SQL dinamico, esattamente come per gli altri parametri. Analogamente, una variabile del tipo di tabella ha lo stesso ambito di qualsiasi altra variabile locale creata utilizzando un'istruzione DECLARE. È possibile dichiarare variabili con valori di tabella all'interno di istruzioni Transact-SQL dinamiche e passarle come parametri con valori di tabella a stored procedure e funzioni.

I parametri con valori di tabella offrono maggiore flessibilità e, in alcuni casi, prestazioni migliori rispetto alle tabelle temporanee o ad altre modalità disponibili per passare un elenco di parametri. I parametri con valori di tabella offrono i vantaggi seguenti:

• Non acquisire blocchi per il popolamento iniziale dei dati da un client.

• Forniscono un modello di programmazione semplice.

• Consentono di includere logica di business complessa in una singola routine.

• Riduci il numero di round trip al server.

• Possono avere una struttura di tabella con cardinalità diversa.

• Sono fortemente tipizzati.

• Consentono al client di specificare tipo di ordinamento e chiavi univoche.

• Vengono memorizzati nella cache come una tabella temporanea quando vengono utilizzati in una stored procedure. A partire da SQL Server 2012, anche i parametri con valori di tabella vengono memorizzati nella cache per le query con parametri.

Restrizioni

Ai parametri con valori di tabella si applicano le restrizioni seguenti:

• SQL Server non gestisce statistiche su colonne di parametri con valori di tabella.

• I parametri con valori di tabella devono essere passati come parametri READONLY di input alle routine Transact-SQL. Non è possibile eseguire operazioni DML, ad esempio UPDATE, DELETE o INSERT, su un parametro con valori di tabella nel corpo di una routine.

• Non è possibile utilizzare un parametro con valori di tabella come destinazione di un'istruzione SELECT INTO o INSERT EXEC. Un parametro con valori di tabella può trovarsi nella clausola FROM di SELECT INTO o nella stringa INSERT EXEC o nella stored procedure.

parametri Table-Valued e operazioni BULK INSERT

L'utilizzo di parametri con valori di tabella è confrontabile con altre modalità di utilizzo di variabili basate su set, ma può spesso risultare più rapido nel caso di set di dati di notevoli dimensioni. Rispetto alle operazioni bulk con un costo di avvio maggiore rispetto ai parametri con valori di tabella, i parametri con valori di tabella offrono prestazioni buone per l'inserimento di meno di 1000 righe.

I parametri con valori di tabella beneficiano dal caching delle tabelle temporanee quando vengono riutilizzati. Questa memorizzazione nella cache delle tabelle consente una migliore scalabilità rispetto alle operazioni BULK INSERT equivalenti. Utilizzando piccole operazioni di inserimento di righe è possibile ottenere un piccolo vantaggio in termini di prestazioni utilizzando elenchi di parametri o istruzioni batch invece di operazioni di BULK INSERT o parametri di tipo tabella. Tali metodi, tuttavia, risultano meno efficaci da programmare e le prestazioni si riducono rapidamente con l'aumentare del numero di righe.

I parametri con valori di tabella garantiscono livelli di prestazioni analoghi o migliori rispetto all'implementazione di una matrice di parametri equivalente.

Esempio

Nell'esempio seguente viene usato Transact-SQL e viene illustrato come creare un tipo di parametro con valori di tabella, dichiarare una variabile per farvi riferimento, compilare l'elenco dei parametri e quindi passare i valori a una stored procedure.

USE AdventureWorks2012;  
GO  
  
/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  
  
/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.Production.Location  
           (Name  
           ,CostRate  
           ,Availability  
           ,ModifiedDate)  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
        GO  
  
/* Declare a variable that references the type. */  
DECLARE @LocationTVP AS LocationTableType;  
  
/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT Name, 0.00  
    FROM AdventureWorks2012.Person.StateProvince;  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  

Vedere anche

CREATE TYPE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
sys.types (Transact-SQL)
sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)