Novinek okolo MS SQL Serveru jsme již přeplněni. Každý viděl podporu pro prostorová data nebo … Co ale tak trochu uniklo pozornosti marketingových materiálů, a je to možná škoda neboť se jedná o zajímavé rozšíření, je nová „fičurka“ nazvaná Table Value Parameters zkráceně TVP.
reklama
Novinek okolo MS SQL Serveru jsme již přeplněni. Každý viděl podporu pro prostorová data nebo … Co ale tak trochu uniklo pozornosti marketingových materiálů, a je to možná škoda neboť se jedná o zajímavé rozšíření, je nová „fičurka“ nazvaná Table Value Parameters zkráceně TVP.
Za pomoci TVP můžete jednoduše jako parametr předat tabulku. Jedno z prvních využití, které vás (tedy alespoň mě ano) je předání více hodnot/řádků do uložené procedury. Kolikrát už jsem potřeboval uložit uživatele a jeho příslušnost do skupin „na jeden zátah“. Nyní takovýto postup možný bez použití dočasné tabulky nebo předávání XML. Abychom si fungování ukázali, vytvoříme si jednoduchou tabulku:
create table test(
id int not null primary key,
s nvarchar(200),
y datetime
);
Zadeklarujeme si proměnnou stejného typu jako řádek této tabulky a naplníme ji:
declare @myRow as table (id int, s nvarchar(200), y datetime);
insert into @myRow values (10, 'abc', CURRENT_TIMESTAMP);
Data z této proměnné nyní můžeme vložit do naší tabulky:
insert into test(id, s, y) select * from @myRow;
Až potud to není zase o moc velké ulehčení oproti dočasné tabulce. Zadeklarujme si přímo nový typ:
create type testRow as table (id int, s nvarchar(200), y datetime);
Tímto jsme si vytvořily nový typ, který může nést hodnoty odpovídající řádkům tabulky test. Samozřejmě jej můžeme používat pro deklarace proměnných jako výše, avšak mnohem zajímavější je využití jako parametru uložené procedury. Vytvořme tedy jednu „velmi sofistikovanou“:
create procedure testSP(@row testRow READONLY)
as
begin
select id + 1, s from @row;
end
a vyzkoušejme:
declare @myRow testRow;
insert into @myRow values (10, 'abc', CURRENT_TIMESTAMP);
insert into @myRow values (11, 'abc', CURRENT_TIMESTAMP);
exec testSP @myRow;
Výsledek, který obdržíme, asi nikoho nepřekvapí. Na druhou stranu, uvědomíme-li si, že jsme jednoduše předali do procedury de facto tabulku, otevírá se nám zcela nová oblast při zpracování dat na MS SQL Serveru.
Podívejme se nyní na omezení, protože s každou skvělou funkcionalitou přichází i jistá omezení. S TVP jsou jen dvě, která stojí za zmínku. Obsah tabulky předané do procedury nemůžete měnit (viz klíčové slovo READONLY v definici) a TVP není možné použít jako výstupní (OUTPUT) parametry.
Budeme-li se chtít podívat detailněji definované TVP, můžeme prozkoumat nový pohled sys.table_types, stejně jako standardní sys.types. Table Value Parameters jsou materializovány v tempdb, takže i je možné zpracovávat i velké objemy dat (ačkoli to není standardní scénář). Proměnné TVP definované na serveru jsou předávány jako reference do tabulky v tempdb, nemusíme se tedy obávat zbytečné režie.
Protože jsme hovořili v předchozím textu pouze v intencích provádění příkazů na serveru, může nejednoho napadnout: „Jak jsou data předávána z klienta? Například z ADO.NET.“. Použití je velmi jednoduché. Jako typ parametru je použit SqlDbType.Structured a data můžeme čerpat z DataTable, DbDataReaderu nebo IListu. Jako .TypeName je třeba uvést název definovaného typu (pokud jej provider není schopen odvodit/získat jinak). Příklad vše osvětlí:
SqlParameter myParam = insertCommand.Parameters.AddWithValue("@row", <someData>);
myParam.SqlDbType = SqlDbType.Structured;
myParam.TypeName = "dbo.testRow";
Jednoduché, čitelné a čisté. Žádné složité a magické konstrukce.
Ačkoli je novinka v podobě Table Value Parameter není tak diskutovaná jako např. každý nechť doplní sám jde bezesporu se o malou, leč užitečnou vlastnost, po které srdce nejednoho vývojáře zaplesá.