Usare parametri con valori di tabella (motore di database)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Not supported. Azure Synapse Analytics Not supported. Piattaforma di strumenti analitici (PDW)

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.

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.

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.
  • Vengono memorizzati nella cache come una tabella temporanea quando vengono utilizzati in una stored procedure. A partire da SQL Server 2012 (11.x), anche i parametri con valori di tabella sono memorizzati nella cache per le query con parametri.

Autorizzazioni

Per creare un'istanza di un tipo di tabella definito dall'utente o chiamare un stored procedure con un parametro con valori di tabella, l'utente deve avere l'autorizzazione EXECUTE e REFERENCES per il tipo oppure per lo schema o il database che contiene il tipo.

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ò essere incluso nella clausola FROM di un'istruzione SELECT INTO o nella stringa o stored procedure INSERT EXEC.

Parametri con valori di tabella 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, 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.

Esempio

L'esempio seguente usa Transact-SQL e illustra come creare un tipo di parametro con valori di tabella, dichiarare una variabile per farvi riferimento, compilare l'elenco di parametri e quindi passare i valori a una stored procedure nel database AdventureWorks.

/* 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 AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Vedi anche