Utilizzare parametri con valori di tabella (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. Un ulteriore vantaggio consiste nel fatto che tali parametri possono essere utilizzati in operazioni basate su set.

In Transact-SQL i parametri con valori di tabella vengono passati 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 o nativi in qualsiasi linguaggio gestito.

Contenuto dell'argomento

Vantaggi

Restrizioni

Parametri con valori di tabella e 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 acquisiscono blocchi per il popolamento iniziale di dati da un client.

• Forniscono un modello di programmazione semplice.

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

• Riducono 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.

[Top]

Restrizioni

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

• In SQL Server non vengono gestite 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ò essere incluso nella clausola FROM di un'istruzione SELECT INTO o nella stringa o stored procedure INSERT EXEC.

[Top]

Parametri con valori di tabellae 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, che comportano costi di avvio maggiori, i parametri con valori di tabella garantiscono livelli di prestazioni ottimali per operazioni di inserimento di non oltre 1.000 righe.

I parametri con valori di tabella riutilizzati possono sfruttare il vantaggio derivante dalla memorizzazione nella cache della tabella temporanea, che consente di ottenere una migliore scalabilità rispetto alle operazioni BULK INSERT equivalenti. Se si eseguono operazioni di inserimento di righe di entità ridotta, è possibile ottenere vantaggi minimi in termini di prestazioni utilizzando elenchi di parametri o istruzioni batch anziché operazioni BULK INSERT o parametri con valori di 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.

[Top]

Esempio

Nell'esempio seguente viene utilizzato Transact-SQL e viene illustrato come creare un tipo di parametro con valori di tabella, dichiarare una variabile per farvi riferimento, riempire un elenco di 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

[Top]

Vedere anche

Riferimento

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)