UGISS User Group Italiano SQL Server
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.
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:
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
