Jeśli w „update” wstawisz zmienne, zyska cechy niecodzienne

Zakładam, że polecenia update języka T-SQL nie trzeba nikomu przedstawiać. Jest to jedno z jego czterech podstawowych poleceń. Służy do aktualizacji zawartości kolumn tabeli. Zakładam jednak, że niewiele osób wie, iż polecenie to potrafi także aktualizować … zmienne! W niniejszym wpisie chciałbym pokazać, jak to działa i jakie korzyści można tym sposobem uzyskać.

Na początek przykład aktualizacji dwóch kolumn tabeli oraz dwóch zmiennych:

if object_id('tempdb..#T') is not null
	drop table #T;
create table #T(a int, b int);
insert into #T values (0, 0);
declare @a int, @b int

update #T
set
	a = 1, b = 2,
	@a = 1, @b = 2;

select @a as [@a], @b as [@b], a, b from #T;

W wyniku wykonania powyższego skryptu uzyskamy następujący wynik.

Jak widać udało się ustawić zarówno kolumny tabeli jak i same zmienne.

Co stanie się jeśli postanowimy wykorzystać wartość kolumny i zmiennej ustawianych jako pierwsze do ustalenia wartości następnej kolumny lub zmiennej?

if object_id('tempdb..#T') is not null
	drop table #T;
create table #T(a int, b int);
insert into #T values (0, 0);
declare @a int, @b int

update #T
set
	a = 1, b = a + 1,
	@a = 1, @b = @a + 1;

select @a as [@a], @b as [@b], a, b from #T;

Wyniki będą następujące:

Jak widać, zachowanie zmiennych jest inne niż zachowanie kolumn. Zmienne są bowiem aktualizowane natychmiast – w momencie podstawiania wartości, a kolumny dopiero w momencie kiedy wszystkie zostaną wypełnione. Z tego względu kolumna b korzystająca z kolumny a otrzymuje jej wartość sprzed modyfikacji.

Skoro zmienne w update uzyskują wartość natychmiast, to sprawdźmy, jak to dokładnie działa. Tym razem zmienne będą posiadać jakieś wartości przez wydaniem polecenia, te zaś zostaną użyte do aktualizacji kolumn tabeli. Na koniec dotychczasowe wartości zmiennych zostaną zmienione na inne.

if object_id('tempdb..#T') is not null
	drop table #T;
create table #T(a int, b int);
insert into #T values (0, 0);
declare @a int, @b int

select @a = -1, @b = 111;
update #T
set
	a = -@a, b = -@a - 1 - @b,
	@b = @a + 1, @a = 11;

select @a as [@a], @b as [@b], a, b from #T;

Tym razem wyniki z pewnością są zaskakujące. Jak się okazuje, do aktualizacji kolumn zostały użyte wartości nadane w poleceniu, a nie te, które ustalono zmiennym tuż przed. Tylko do aktualizacji zmiennej @b użyta została początkowa wartość zmiennej @a. Z tego doświadczenia wynika, że niezależnie od kolejności podstawiania najpierw zostaną zaktualizowane zmienne i dopiero wówczas kolumny. Nie będzie więc możliwe zrealizowanie następującego wariantu:

if object_id('tempdb..#T') is not null
	drop table #T;
create table #T(a int, b int, c int);
insert into #T values (0, 0, 0);
declare @a int;

set @a = 3;
update #T
set
	a = @a, @a = @a * 2, b = @a, @a = @a * 2, c = @a;

select @a as [@a], a, b, c from #T;
GO

Ktoś może powiedzieć „no fajnie, ciekawe efekty, ale czy to daje jakąś korzyść?”. Otóż tak! Pierwsza z brzegu: przyjmijmy, że implementujemy jakiś licznik (np. numer dokumentu handlowego) i teraz chodzi o to, aby jednocześnie pobrać obecną wartość, która może zostać wykorzystana i jednocześnie zwiększyć ją o 1, aby numery się nie powtórzyły (zakładamy, że nie mamy mechanizmu sekwencji). Musi być to operacja atomowa, więc konieczne będzie użycie transakcji.

begin transaction
update Numerator set Nr = Nr where Typ = @Typ;
select @Nr = Nr from Numerator where Typ = @Typ;
update Numerator set Nr = @Nr + 1 where Typ = @Typ;
commit

Pierwszy update jest po to, aby zablokować dany wiersz do edycji, potem następuje odczyt wartości, a następnie właściwy update. Można zrealizować to jeszcze w inny sposób – chodzi jednak o pokazanie, że konieczne jest wiele poleceń i użycie transakcji.

Jeśli jednak skorzystamy z właśnie nabytej wiedzy, to całość powyższego skryptu da się uprościć do jednego polecenia (bez transakcji – jedno polecenie jest atomowe samo w sobie):

update Numerator set @Nr = Nr, Nr = Nr +1 where Typ = @Typ;

Co ciekawe, to samo daje się zrealizować w poleceniu merge. Załóżmy, że nasz numerator jest uzależniony od roku i jeśli ulega on zmianie – należy zacząć dla niego dedykowaną numerację.

set @Nr = 1; -- założenie, że rok się zmienił i mamy numerowanie od 1
merge Numerator
using (select @Typ as T, Year(GetDate()) R) as Q on R = Rok and T = Typ
when matched then
	update set @Nr = Nr, Nr = Nr + 1 -- pobranie numeru i zwiększenie numeru
when not matched then
	insert (Rok, Nr) values (R, 2); -- kolejny nr do wykorzystania to 2, 1 podstawiono wyżej

Czy jest jeszcze jakieś ciekawy sposób na wykorzystanie wspólnej aktualizacji zmiennych i kolumn tabeli? Tak. Można np. wyliczyć sumę narastająco (jeżeli nie dysponujemy wersją 2012 SQL Server, gdzie można to zrobić wydajnie w poleceniu select). Poniższy skrypt demonstruje takie wyliczenie, dla tabeli tymczasowej wypełnianej przykładowymi danymi.

if object_id('tempdb..#T') is not null
	drop table #T;
create table #T (symbol varchar(10), value int, total int)
declare @Count int = 160160;
while @Count > 0
begin
	set @Count = @Count - 1;
	insert into #T
	values ('Item' + LTrim(Str(@Count)), @Count % 2 + 1, null);
end;

declare @total int = 0;
update T1
set @total = @total + T1.value, total = @total
from #T T1

select * from #T;

To zapewne nie wszystkie możliwości, jakie otwierają się podczas jednoczesnej aktualizacji zmiennych i kolumn. Warto więc pamiętać o przedstawionej w tym wpisie umiejętności polecenia update, bo być może pozwoli ona znaleźć wygodniejsze rozwiązanie.