Home > Articoli > Table-Valued Parameters in Action

Table-Valued Parameters in Action

SQL Server 2008 recepisce una richiesta che gli sviluppatori avevano inoltrato a Microsoft già da parecchio tempo, ossia la possibilità di passare a stored procedure e function definite dall’utente, parametri di tipo tabella ovvero Table-Valued Parameters (TVPs).

Nelle precedenti versioni di SQL Server, la necessità di passare una struttura dati come parametro ad una stored procedure o ad una function veniva spesso risolta con un workaround che consiste nel passare parametri di tipo XML o Comma Separeted String. Queste soluzioni, tipicamente, richiedono però la stesura di parecchio codice T-SQL che difficilmente potrà scalare in modo ottimizzato in funzione della quantità dei dati trattati.

Table-Valued Parameters, invece, essendo parametri fortemente tipizzati e generalmente più efficienti di quelli di tipo XML e Comma Separeted String permettono di scrivere meno codice, meno complesso e decisamente più performante, basti pensare alle ottimizzazioni che SQL Server 2008 ha implementato in tabular data stream (TDS) per il trasporto di grandi quantità di dati o TVPs.

I benefit che derivano dall’utilizzo di Table-Valued Parameters sono quindi:

  • Miglioramento del codice T-SQL e della programmazione di user-defined stored procedure e function
  • Miglioramento delle performance, specialmente quando vengono trattati grandi quantità di dati
    • Minor numero di roundtrip tra client e server
    • Potenzialmente passaggio di dati illimitato
    • Tabular data stream (TDS) ottimizzato

Vediamo ora quali sono gli step necessari per utilizzare Table-Valued Parameters in uno scenario di esempio che vede la creazione di una stored procedure utilizzata applicare l’inventario dei prodotti giacenti a magazzino. L’inventario potrebbe essere eseguito “a rotazione” o “globalmente” in funzione del periodo dell’anno. I dati raccolti da ogni operatore, dopo gli opportuni controlli, dovranno confluire all’interno di una variabile tabella che verrà successivamente passata alla stored procedure che si occuperà di eseguire il MERGE tra l’inventario logico (giacenze nel sistema) e l’inventario fisico (giacenze fisicamente rilevate).

 

Definizione del tipo di dato tabella: user-defined table type

Il primo step consiste nella definizione del tipo di dato tabella (user-defined table type), che verrà successivamente utilizzato come tipo nella definizione delle variabili da passare come parametri a stored procedure o function. Dopo aver creato il database di prova SS2008_TVPs e la tabella dbo.ProductInventory, il seguente frammento di codice T-SQL implementa la creazione del nuovo tipo di dato tabella dbo.UDT_TabInventory.

————————————————————————
— Setup DB
————————————————————————
use [master];
go

if exists(select * from sys.databases where name = ‘SS2008_TVPs’)
begin
 alter database [SS2008_TVPs] set single_user with rollback immediate;

 drop database [SS2008_TVPs];
end
go

— Create database
create database [SS2008_TVPs];
go

— change database context
use [SS2008_TVPs];
go

————————————————————————
— Setup table
————————————————————————

— dbo.ProductInventory
if OBJECT_ID(‘dbo.ProductInventory’, ‘U’) is not null
  drop table dbo.ProductInventory;
go

create table dbo.ProductInventory
(
  InventoryID int identity(1, 1) not null
  ,ProductID varchar(25) not null
  ,LocationID varchar(20) not null
  ,Quantity decimal(9, 2) not null
  ,ModifiedDate date not null
  ,Status bit not null default 1
  ,constraint PK_ProductInventory primary key(ProductID, LocationID)
);
go

————————————————————————
— User-defined table type
————————————————————————

if exists (select * from sys.table_types
           where name = ‘UDT_TabInventory’
           and schema_id = SCHEMA_ID(‘dbo’))
  drop type dbo.UDT_TabInventory;
go

create type dbo.UDT_TabInventory as table
(
  ID int identity(1, 1) not null primary key(ID)
  ,ProductID varchar(25) not null
  ,LocationID varchar(20) not null
  ,Quantity decimal(9, 2) not null
  ,ModifiedDate date not null default getdate()
);
go

Ora si potrà quindi definire una variabile "di tipo" dbo.UDT_TabInventory la cui struttura è illustrata in figura 1.

declare @VarInventory as dbo.UDT_TabInventory;
go

Figura 1 – User-defined Table Type dbo.UDT_TabInventory

La DMV sys.table_types permette di consultare le proprietà dei tipi di dato tabella definiti dall’utente:

select
  name
  ,is_user_defined
  ,is_table_type
from sys.table_types
go

Figura 1b – Output ottenuto interrogando la DMV sys.table_types

 

Utilizzo di Table-Valued Parameters

Un tipico esempio di utilizzo di TVPs è rappresentato dalla stored procedure dbo.usp_merge_inventory che permette di applicare alla tabella dbo.ProductInventory gli aggiornamenti necessari ad allineare le giacenze logiche alle giacenze fisiche rilevate durante un’operazione di inventario.

La stored procedure accetta in input un parametro di tipo dbo.UDT_TabInventory (Table-Valued Parameters) ossia una tabella contenente i prodotti inventariati dettagliati per locazione e quantità. L’output è rappresentato dall’aggiornamento della tabella dbo.ProductInventory e da un result-set che evidenzia il tipo di aggiornamento eseguito.

Il seguente codice T-SQL permette di creare la stored procedure dbo.usp_merge_inventory:

if OBJECT_ID(‘dbo.usp_merge_inventory’, ‘P’) is not null
  drop procedure dbo.usp_merge_inventory;
go

create procedure dbo.usp_merge_inventory
(
  @Par_Inventory as [dbo].[UDT_TabInventory] READONLY — TVP solo READONLY!!
)
as
begin
  begin tran;

  begin try
    MERGE INTO dbo.ProductInventory as itarget

    USING @Par_Inventory as isource
      on ((itarget.ProductId = isource.ProductId)
          and (itarget.LocationId = isource.LocationId))

    WHEN MATCHED AND
      (isource.Quantity <> 0)
      and ((itarget.Quantity <> isource.Quantity)
           or (itarget.ModifiedDate <> isource.ModifiedDate)) THEN
      update set
        itarget.Quantity = isource.Quantity
        ,itarget.ModifiedDate = isource.ModifiedDate

    WHEN MATCHED AND
      (isource.Quantity = 0) THEN
      delete

    WHEN NOT MATCHED THEN
      insert
      (
        ProductId
        ,LocationId
        ,Quantity
        ,ModifiedDate
      )
      values
      (
        isource.ProductId
        ,isource.LocationId
        ,isource.Quantity
        ,isource.ModifiedDate
      )
    WHEN NOT MATCHED BY SOURCE THEN
      update set
        status = 0
    OUTPUT
      $action as merge_action
      ,inserted.ProductId as IProductId
      ,inserted.LocationId as ILocationId
      ,inserted.Quantity as IQuantity
      ,inserted.ModifiedDate as IModifiedDate
      ,deleted.ProductId as DProductId
      ,deleted.LocationId as DLocationId
      ,deleted.Quantity as DQuantity
      ,deleted.ModifiedDate as DModifiedDate;
  end try
 
  begin catch
    rollback tran
  end catch
 
  commit tran
end;
go

La chiave READONLY indica che il parametro @Par_Inventory non può essere aggiornato o modificato all’interno della definizione della stored procedure, con i parametri di tipo tabella definiti dall’utente, la parola chiave READONLY deve essere specificata.

 

TVPs in action

Popoliamo la tabella dbo.ProductInventory con alcuni dati di test, che rappresentano le giacenze logiche memorizzate prima dell’inventario, utilizziamo il seguente frammento di codice T-SQL:

insert into dbo.ProductInventory
(ProductID, LocationID, Quantity, ModifiedDate)
values
(‘ravioli’,    ‘b002’, 15, ‘20091230’),
(‘chocolade’,  ‘f015’, 30, ‘20091230’),
(‘coffee’,     ‘h001’,  4, ‘20091231’),
(‘mascarpone’, ‘d214’,  6, ‘20091231’);
go

select * from dbo.ProductInventory order by InventoryID;

In output il contenuto della tabella dbo.ProductInventory:

Figura 2 – Tabella dbo.ProductInventory prima dell’inventario

Ipotizziamo di aver eseguito l’inventario, i dati raccolti vengono memorizzati nella variabile tabella @Inventory_on_20100515, pronti per essere passati alla stored procedure dbo.usp_merge_inventory. Il seguente codice T-SQL allestisce la tabella @Inventory_on_20100515 ed esegue la stored procedure.

declare @Inventory_on_20100515 as [dbo].[UDT_TabInventory];

insert into @Inventory_on_20100515
(ProductID, LocationID, Quantity)
values
(‘PIZZA’, ‘AY01’, 40), (‘PIADINA’, ‘AX02’, 70), (‘COFFEE’, ‘H001’, 0);

execute dbo.usp_merge_inventory @Inventory_on_20100515;
go

L’output che si ottiene è illustrato nelle figure 3 e 4.

Figura 3 – Output della stored procedure dbo.usp_merge_inventory (Risultati)

Figura 4 – Output della stored procedure dbo.usp_merge_inventory (Messaggi)

Nella scheda messaggi osserviamo che per aggiornare i dati è stato eseguito un solo roundtrip, che ha interessato 3 righe. L’applicazione dell’inventario ha portato l’inserimento di due righe, rispettivamente per i prodotti PIZZA e PIADINA, il prodotto COFFEE è stato invece eliminato perché non rilevato durante i controlli fisici.

 

Conclusioni

E’ doveroso citare un’ultima caratteristica di TVPs: la loro estrema facilità d’uso. Inoltre, vi invito a provate Table-Valued Parameters con grandi quantità di dati, i risultati saranno sorprendenti.

 

Pulizia DB

————————————————————————
— Cleanup DB
————————————————————————

use [master];
go

if exists(select * from sys.databases where name = ‘SS2008_TVPs’)
begin
 alter database SS2008_TVPs set single_user with rollback immediate;

 drop database SS2008_TVPs;
end
go

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Unit testing: Come scrivere la tua prima unit test!

Nell’articolo precedente, il secondo di questa serie, abbiamo descritto come installare il framework tSQLt, il …