T-SQL – nowości dla programistów, cz. 1/2

Autor: Wojciech Kalbarczyk

Opublikowano: 2010-11-10

T-SQL (Transact-SQL) jest rozszerzeniem języka SQL stosowanym dla serwerów Microsoft SQL Server. Pozwala on tworzyć takie elementy, jak pętle, instrukcje warunkowe oraz zmienne. Dzięki temu w T-SQL mamy możliwość tworzenia procedur, funkcji składowych i wyzwalaczy. Wszystko to daje w sumie ogromne możliwości programistom SQL. Oczywiście twórcy nadal rozwijają to rozszerzenie, stale dodając nowe opcje i ułatwienia. Postaram się w tym artykule opisać główne zmiany wprowadzone w SQL Server 2008.

            Przede wszystkim zmieniono instrukcję INSERT. Jak wiemy, służy ona do tworzenia nowych wierszy w tabelach i widokach. Do niedawna, chcąc wprowadzić więcej niż jeden wiersz, musieliśmy do każdego nowego wiersza pisać oddzielną instrukcję INSERT. Po wprowadzeniu zmian wystarczy tylko raz użyć tej instrukcji, aby dodać jednocześnie kilka wierszy.

W poprzednich wersjach SQL Server należało zrobić to w następując sposób:

Przykład 1.

DECLARE @tablica TABLE

(

                a INT NULL,

                b INT NULL

)

INSERT INTO @tablica VALUES(1,1)

INSERT INTO @tablica VALUES(2,2)

Po wprowadzeniu zmian w T-SQL nasz kod może wyglądać następująco:

Przykład 2.

DECLARE @tablica TABLE

(

                a INT NULL,

                b INT NULL

)

INSERT INTO @tablica VALUES(1,1),(2,2)

            Kolejna zmiana została wprowadzona do operatora przypisania. Możemy teraz dodatkowo wykonywać operacje dodawania, odejmowania, mnożenia i dzielenia za pomocą operatorów '+=', '-=', '*=', '/=' znanych z języków C, C++, Visual Basic czy JavaScript. 

Poniższy przykład pokazuje, jak należało wykonywać te operacje w poprzednich wersjach serwera, a jak w obecnej:

Przykład 3.

DECLARE @zmienna INT

SET @zmienna =0

SELECT @zmienna=@zmienna+1

SELECT @zmienna AS W_poprzednich_wersjach

SELECT @zmienna+=9

SELECT @zmienna AS W_SQL_SERVER_2008

Obraz 1. Operator przypisania.

            Następna zmiana dotyczy sposobu inicjowania zmiennych. W starszych wersjach SQL Server do tworzenia zmiennej i nadawania jej wartości musieliśmy najpierw użyć instrukcji DECLARE, aby stworzyć zmienną, a następnie SET, aby nadać jej wartość. Po wprowadzeniu zmian w T-SQL możemy nadać zmiennej wartość początkową za pomocą instrukcji DECLARE.

Poniższy przykład pokazuje różnicę w sposobie inicjowania zmiennych:

Przykład 4.

DECLARE @zmienna1 INT

SET @zmienna1 =0

SELECT @zmienna1 AS W_poprzednich_wersjach_serwera

DECLARE @zmienna2 INT =1

SELECT @zmienna2 AS W_obecnej_wersji_serwera

Obraz 2. Inicjowanie zmiennych.

            Następną modyfikacją było dodanie nowej instrukcji – MERGE. Pozwala ona na wykonanie w ramach jednej instrukcji takich operacji, jak dodawanie, usuwanie oraz aktualizowanie wierszy. Instrukcja ta wymaga dwóch tabel – docelowej oraz źródłowej. Na wstępie porównywana jest zawartość obu tabel, po czym dzięki instrukcjom WHEN MATCHED oraz WHEN NOT MATCHED możemy określić, co ma się wydarzyć z rekordami znajdującymi się w obu tabelach (dla WHEN MATCHED) lub z danymi z tabeli docelowej niemającymi swojego odpowiednika w tabeli źródłowej.

Aby łatwiej było zrozumieć jak działa operator GROUPING SETS, stwórzmy dwie tabele:

Przykład 5.

CREATE TABLE #kolekcje_znaczkow

(

id INT NOT NULL,

nazwa VARCHAR(50),

liczba_znaczkow INT

)

CREATE TABLE #zmiana

(

id INT NOT NULL,

data_zmiany DATE,

zmiana_stanu INT

)

Tabela „#kolekcje_znaczkow” przechowuje informacje o posiadanych znaczkach, natomiast w tabeli „#zmiana” będą informacje o zmianach zachodzących w naszych kolekcjach znaczków, a więc informacje o tym, ile znaczków nam przybyło bądź ubyło. Dodajemy do tabel przykładowe informacje:

Przykład 6.

INSERT INTO #kolekcje_znaczkow

VALUES(1,'polskie',120),(2,'zagraniczne',340)

SELECT * FROM  #kolekcje_znaczkow

INSERT INTO #zmiana

VALUES(1,GETDATE(),20),(2,GETDATE(),-340)

Następnie tworzymy zapytanie, które na podstawie danych w tabeli „#zmiana” zaktualizuje tabelę „#kolekcje_znaczkow”. Jeżeli liczba znaczków w kolekcji spadnie do zera, to kolekcja jest usuwana. Jeżeli znaczki zostaną dodane do kolekcji, której wcześniej nie posiadaliśmy, to zostanie ona utworzona. Jeżeli nie zajdzie żaden z powyższych dwóch przypadków, to zostanie zaktualizowana jedynie kolumna przechowująca informacje o liczbie znaczków w kolekcji. W starszych wersjach SQL Server wyglądałoby to tak:

Przykład 7.

UPDATE k_z

SET k_z.liczba_znaczkow = k_z.liczba_znaczkow+z.z_s         

FROM #kolekcje_znaczkow k_z JOIN

(

                                SELECT id,SUM(zmiana_stanu) AS z_s

                                FROM #zmiana

                                GROUP BY id     

) z

ON k_z.id=z.id

INSERT INTO #kolekcje_znaczkow(id,liczba_znaczkow)

SELECT id,SUM(zmiana_stanu)

FROM #zmiana z

WHERE NOT EXISTS (SELECT * FROM #kolekcje_znaczkow k_z WHERE k_z.id=z.id)

GROUP BY id

DELETE FROM #kolekcje_znaczkow WHERE liczba_znaczkow < 0

SELECT * FROM #kolekcje_znaczkow

Obraz 3. Wynik zapytania aktualizującego tabelę bez instrukcji MERGE.

Natomiast przy wykorzystaniu instrukcji MERGE kod wygląda tak:

Przykład 8.

MERGE #kolekcje_znaczkow k_z

USING (

SELECT id,SUM(zmiana_stanu) AS z_s

                                FROM #zmiana

                                GROUP BY id     

) z

ON k_z.id=z.id

WHEN MATCHED AND k_z.liczba_znaczkow + z.z_s = 0 THEN

                 DELETE

WHEN MATCHED THEN

                UPDATE                SET k_z.liczba_znaczkow += z.z_s

WHEN  NOT MATCHED BY TARGET THEN

                INSERT (id,liczba_znaczkow)

                VALUES (z.id,z.z_s)

;

SELECT * FROM #kolekcje_znaczkow

Obraz 4. Wynik zapytania aktualizującego tabelę z instrukcją MERGE.

Widzimy, że sam kod nie jest wiele krótszy od poprzedniego, ale jest dużo czytelniejszy i tylko raz odwołuje się do tablicy „#kolekcje_znaczkow” (a nie trzy razy, jak to miało miejsce w pierwszym zapytaniu).

            Inną nowością jest dodanie operatora GROUPING SETS. W poprzednich wersjach serwera, chcąc pogrupować dane na kilka sposobów musieliśmy za każdym razem odczytywać dane z bazy oraz grupować je, korzystając z GROUP BY. Nie mogliśmy również pozostawić klauzuli GROUP BY pustej. Zmieniło się to w T-SQL obsługiwanym przez SQL Server 2008, gdzie możemy już pozostawić pustą klauzulę GROUP BY, czego efektem jest jedynie zwiększona czytelność kodu, bowiem pusta klauzula jest równoznaczna z wpisaniem 'GROUP BY ( )'. Większą funkcjonalność uzyskaliśmy natomiast dzięki wprowadzeniu operatora GROUPING SETS. Możemy teraz grupować dane na kilka sposobów, przy czym wystarczy tylko raz odczytać dane z tabeli. Dzięki temu otrzymujemy dużo czytelniejszy kod, który serwer przetwarza o wiele szybciej niż poprzednio. Warto wspomnieć, że możemy w jednym zapytaniu wielokrotnie korzystać z operatora GROUPING SETS, a w rezultacie otrzymujemy iloczyn kartezjański wszystkich podgrup.

Aby łatwiej było zrozumieć jak działa GROUPING SETS, stwórzmy tabelę oraz wypełnijmy ją przykładowymi danymi:

Przykład 9.

DECLARE @tablica TABLE

(

                a int,

                b int

)

INSERT INTO @tablica

VALUES(1,1),(1,2),(4,2),(1,2)

Następnie stwórzmy zapytanie, które posortuje dane na różne sposoby. Do niedawna musieliśmy to robić w następujący sposób:

Przykład 10.

SELECT a,b,SUM(a+b) AS suma

FROM @tablica

GROUP BY a,b

UNION ALL

SELECT a, NULL,SUM(a+b) AS suma

FROM @tablica

GROUP BY a

UNION ALL

SELECT NULL,b,SUM(a+b) AS suma 

FROM @tablica

GROUP BY b

Obraz 5. Grupowanie bez operatora GROUPING SETS.

Dzięki operatorowi GROUPING SETS analogiczne zapytanie wygląda następująco:

Przykład 11.

SELECT a,b,SUM(a+b) AS SUMA

FROM @tablica

GROUP BY GROUPING SETS(

(a,b),(a),(b))

Obraz 6. Grupowanie z użyciem operatora GROUPING SETS.

---