Parametri con valori di tabella (Motore di database)
I parametri con valori di tabella costituiscono un nuovo tipo di parametro introdotto in SQL Server 2008 e 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.
Nota
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.
Creazione e utilizzo di parametri con valori di tabella in Transact-SQL
I parametri con valori di tabella includono due componenti principali, ovvero un tipo di SQL Server e un parametro che fa riferimento a tale tipo. Per creare e utilizzare parametri con valori di tabella, effettuare le operazioni seguenti:
Creare un tipo di tabella e definire la struttura della tabella.
Per informazioni su come creare un tipo di SQL Server, vedere Tipi di tabella definiti dall'utente. Per ulteriori informazioni su come definire la struttura di una tabella, vedere CREATE TABLE (Transact-SQL).
Dichiarare una routine che include un parametro del tipo di tabella. Per ulteriori informazioni sulle routine di SQL Server, vedere CREATE PROCEDURE (Transact-SQL) e CREATE FUNCTION (Transact-SQL).
Dichiarare una variabile del tipo di tabella e fare riferimento al tipo di tabella. Per informazioni sulla dichiarazione delle variabili, vedere DECLARE @local_variable (Transact-SQL).
Riempire la variabile di tabella utilizzando un'istruzione INSERT. Per ulteriori informazioni su come inserire dati, vedere Aggiunta di righe con INSERT e SELECT.
Dopo avere creato e riempito la variabile di tabella, è possibile passare la variabile a una routine.
Quando la routine non è più compresa nell'ambito, il parametro con valori di tabella non è più disponibile. La definizione del tipo viene invece mantenuta fino a quando non viene eliminata.
Per utilizzare un parametro con valori di tabella in SQL Server Native Client, vedere Parametri con valori di tabella (SQL Server Native Client).
Per utilizzare un parametro con valori di tabella in ADO.NET, vedere la documentazione di ADO.NET.
Vantaggi
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.
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.
Ambito
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.
Sicurezza
Le autorizzazioni per i parametri con valori di tabella si basano sul modello di sicurezza a oggetti per SQL Server e utilizzano le parole chiave Transact-SQL, ovvero CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION e REVOKE.
Viste del catalogo
Per ottenere le informazioni associate ai parametri con valori di tabella, è possibile eseguire query sulle viste del catalogo sys.parameters (Transact-SQL), sys.types (Transact-SQL) e sys.table_types (Transact-SQL).
Confronto tra 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.
Nella tabella seguente viene illustrata la tecnologia da utilizzare in base alla velocità delle operazioni di inserimento.
Origine dati |
Logica del server |
Numero di righe |
Tecnologia ottimale |
|---|---|---|---|
File di dati formattato nel server |
Inserimento diretto |
< 1000 |
BULK INSERT |
File di dati formattato nel server |
Inserimento diretto |
> 1000 |
BULK INSERT |
File di dati formattato nel server |
Complessa |
< 1000 |
Parametri con valori di tabella |
File di dati formattato nel server |
Complessa |
> 1000 |
BULK INSERT |
Processo client remoto |
Inserimento diretto |
< 1000 |
Parametri con valori di tabella |
Processo client remoto |
Inserimento diretto |
> 1000 |
BULK INSERT |
Processo client remoto |
Complessa |
< 1000 |
Parametri con valori di tabella |
Processo client remoto |
Complessa |
> 1000 |
Parametri con valori di tabella |
Esempi
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 AdventureWorks2008R2;
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 usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[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
[AdventureWorks2008R2].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO