Akademia SQL - Część 12: Nowości w T-SQL
 |
Zawartość strony
 | Nowości w T-SQL – informacje podstawowe |
| Nowości w T-SQL – laboratorium |
| Nowości w T-SQL – referencje |
Nowości w T-SQL – informacje podstawowe
T-SQL to zaimplementowana w serwerach SQL wersja strukturalnego języka zapytań (ang. Structured Query Language), który pozwala odczytywać i zmieniać dane oraz tworzyć i modyfikować obiekty bazodanowe, takie jak tabele czy procedury.
W serwerze SQL 2008 rozszerzono nieco składnię i możliwości języka T-SQL, a najważniejsze zmiany omówione w trakcie tej lekcji, to:
1. | Szybka inicjalizacja zmiennych, |
2. | Skrócone operatory przypisania, |
3. | Konstruktor wierszy, |
4. | Operator GROUPING SETS, |
5. | Instrukcja MERGE. |
Wymieniona lista nie wyczerpuje wszystkich nowości, które zawarto w języku T-SQL w serwerze SQL 2008. Pozostałym nowościom — nowym typom daty i czasu, parametrom i typom tablicowym czy typowi danych hierarchyid poświęcone zostały osobne lekcje Akademii SQL.
Szybka inicjalizacja zmiennych pozwala na wstawienie wartości do zmiennej już w momencie jej tworzenia, a nie dopiero za pomocą operatora SET czy instrukcji SELECT.
Podstawowym operatorem przypisania wartości języka T-SQL jest =. Jego umieszczenie w instrukcji SELECT lub SET powoduje przypisanie wyrażeniu znajdującemu się z lewej strony wartość z prawej strony znaku równości. W wersji 2008 dodane zostały następujące skrócone operatory przypisania:
1. | += (dodaj i przypisz), |
2. | -= (odejmij i przypisz), |
3. | *= (pomnóż i przypisz), |
4. | /= (podziel i przypisz), |
5. | %= (oblicz modulo i przypisz), |
6. | ^= (alternatywa bitowa). |
Konstruktor wierszy umożliwia używanie klauzuli VALUES do definiowania wielu wartości. Pozwala to na jednoczesne wstawienie jedną instrukcją INSERT wielu wierszy.
Klauzula GROUP BY, która jest znana z wcześniejszych wersji systemu, pozwala zdefiniować jedną hierarchię grup, dla których będą wywoływane funkcje grupujące. Nowy operator GROUPING SETS pozwala określić dowolnie wiele sposobów dzielenia danych na grupy i podgrupy. Eliminuje to konieczność wykonywania wielu złączonych operatorem UNION ALL zapytań, które mogą zostać zastąpione jednym.
Instrukcja MERGE pozwala jednocześnie wykonać operacje wstawiania, usuwania lub aktualizacji różnych wierszy, czyli łączy funkcje instrukcji INSERT, DELETE i UPDATE. Możliwe jest więc szybkie zsynchronizowanie danych zapisanych w różnych tabelach lub wykonanie operacji warunkowego wstawiania wierszy nieistniejących w tabeli docelowej i aktualizacji danych, które już są w tej tabeli.
Nowości w T-SQL – laboratorium
Celem laboratorium jest praktyczne zapoznanie się z nowościami wprowadzonymi do języka T-SQL w serwerze SQL 2008. Laboratorium składa się z 5 zadań, które odpowiadają omówionym w części teoretycznej zagadnieniom.
Zadanie 1
Celem zadania jest zapoznanie się z funkcjonalnością szybkiej inicjalizacji zmiennej, która ma miejsce w momencie jej tworzenia.
1. Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.
2. Uruchom nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz i uruchom poniższy kod, który pokaże jeden ze sposobów na inicjalizację zmiennej w starszych wersjach serwera SQL:
DECLARE @p INT SELECT @p AS [P przed inicjalizacja] SET @p=3 SELECT @P AS [P po inicjalizacji] -------- P przed inicjalizacja --------------------- NULL (1 row(s) affected) P po inicjalizacji ------------------ 3 (1 row(s) affected)
Jak widać, zmienna @p po zadeklarowaniu na wartość NULL i trzeba użyć operatora SET (w tym przypadku), aby ustawić wartość zmiennej. Nie jest to rozwiązanie optymalne chociażby ze względu na fakt, że mnoży niepotrzebnie linie kodu.
3. Wpisz i uruchom poniższy kod, który zadeklaruje i zainicjalizuje zmienną @i, która jest typu INT:
DECLARE @i INT = 5 SELECT @i AS [I po szybkiej inicjalizacji] --- I po szybkiej inicjalizacji --------------------------- 5 (1 row(s) affected)
4. Dla zainicjalizowania zmiennej można wykorzystać funkcje systemowe, jak pokazano poniżej:
DECLARE @date DATE = GETDATE() SELECT @date AS [date po szybkiej inicjalizacji] --- date po szybkiej inicjalizacji ------------------------------ 2008-10-01 (1 row(s) affected)
5. Inicjalizując zmienna można również użyć rzutowania jej wartości na odpowiedni typ:
DECLARE @k INT = CAST('100' AS INT)
SELECT @k as [K po szybkiej inicjalizacji]
---
K po szybkiej inicjalizacji
---------------------------
100
(1 row(s) affected)
DECLARE @l INT = CAST('100' AS VARCHAR(5))
SELECT @l as [L po szybkiej inicjalizacji]
---
L po szybkiej inicjalizacji
---------------------------
100
(1 row(s) affected)Zadanie 2
Celem zadania jest pokazanie możliwości oferowanej przez skrócone operatory przypisania.
1. Uruchom nowe okno zapytania (skrót klawiszowy CTRL+N), wpisz i uruchom poniższy kod, który pokaże jeden ze sposobów na przypisywanie wartości zmiennym w starszych wersjach serwera:
DECLARE @x INT SET @x=1 SELECT @x = @x+5 -- wartość @x to teraz 6 SELECT @x = @x-10 -- wartość @x to teraz -4 SELECT @x = @x*4 -- wartość @x to teraz -16 SELECT @x = @x/2 -- wartość @x to teraz -8 SELECT @x ----------- -8 (1 row(s) affected)
2. Na poniższych przykładach pokazaliśmy, w jaki sposób skorzystać z nowych możliwości oferowanych przez operatory szybkiego przypisywania:
DECLARE @x INT = 1 SELECT @x += 5 -- wartość @x to teraz 6 SELECT @x -= 10 -- wartość @x to teraz -4 SELECT @x *= 4 -- wartość @x to teraz -16 SELECT @x /= 2 -- wartość @x to teraz -8 SELECT @x ----------- -8 (1 row(s) affected)
Zadanie 3
Celem zadania jest przedstawienie, w jaki sposób należy używać konstruktora wierszy.
1. Uruchom nowe okno zapytania (skrót klawiszowy CTRL+N), wpisz kod oraz uruchom poniższy kod, który wstawia wiersze do tablicy tymczasowej. Metoda ta jest znana ze starszej wersji systemu i polega na wykonani instrukcji INSERT tyle razy, ile wierszy należy wstawić:
DECLARE @tablica TABLE ( kolumnaA int null ,kolumnaB int null ,kolumnaC int null ) INSERT INTO @tablica VALUES(1,1,1) INSERT INTO @tablica VALUES(10,11,12) INSERT INTO @tablica VALUES(20,21,22) INSERT INTO @tablica VALUES(30,31,32) SELECT * FROM @tablica --- kolumnaA kolumnaB kolumnaC ----------- ----------- ----------- 1 1 1 10 11 12 20 21 22 30 31 32 (4 row(s) affected)
2. Przepisz i uruchom zaprezentowany poniżej kod, który pozwala wstawić wiele wierszy do tablicy tymczasowej używając tylko jednej instrukcji INSERT:
DECLARE @tablica_1 TABLE ( kolumnaA int null ,kolumnaB int null ,kolumnaC int null ) INSERT INTO @tablica_1 VALUES(100,101,102),(110,111,112),(121,122,123) SELECT * FROM @tablica_1 kolumnaA kolumnaB kolumnaC ----------- ----------- ----------- 100 101 102 110 111 112 121 122 123 (3 row(s) affected)
Zadanie 4
Celem zadania jest przedstawienie, w jaki sposób można użyć operatora GROUPING SETS. Pokażemy także, jakie zalety niesie zastosowanie nowej metody.
1. Uruchom nowe okno zapytania (skrót klawiszowy CTRL+N), wpisz oraz uruchom poniższy kod, który wyświetla wartości zamówień wykonanych przez sprzedawcę o identyfikatorze = 288 w roku 2004, według miesiąca, miejsca i klienta, według miesiąca i miejsca oraz według miesiąca i klienta a ostatnie zapytanie zwraca zbiorcze podsumowanie. Wymaga to wykonania 4 zapytań:
USE AdventureWorks GO SELECT MONTH(OrderDate),TerritoryID ,CustomerID,SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE SalesPersonID=288 AND YEAR(OrderDate)=2004 GROUP BY MONTH(OrderDate),TerritoryID,CustomerID UNION ALL SELECT MONTH(OrderDate),TerritoryID,NULL,SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE SalesPersonID=288 AND YEAR(OrderDate)=2004 GROUP BY MONTH(OrderDate),TerritoryID UNION ALL SELECT MONTH(OrderDate),NULL,CustomerID,SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE SalesPersonID=288 AND YEAR(OrderDate)=2004 GROUP BY MONTH(OrderDate),CustomerID UNION ALL SELECT NULL,NULL,NULL,SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE SalesPersonID=288 AND YEAR(OrderDate)=2004
Takie rozwiązanie jest mało wydajne. Serwer bazodanowy czterokrotnie odczytuje te same dane, co przy dużych tabelach wiąże się z długim czasem wykonania zapytania.
2. Możliwość zdefiniowania wielu sposobów grupowania tych samych danych daje operator GROUPING SETS. Poniżej pokazane zapytanie zwraca te same informacje, ale tym razem tabela Sales.SalesOrderHeader odczytana została tylko raz:
SELECT MONTH(OrderDate),TerritoryID ,CustomerID,SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE SalesPersonID=288 AND YEAR(OrderDate)=2004 GROUP BY GROUPING SETS ( (MONTH(OrderDate), TerritoryID,CustomerID), (MONTH(OrderDate), TerritoryID), (MONTH(OrderDate), CustomerID), () );
3. Zalety nowego podejścia uwidaczniają się podczas analizy planów zapytań. Pierwsza metoda jest prawie cztery razy wolniejsza od opartej na nowym operatorze GROUPING SETS.
Zadanie 5
W ostatnim zadaniu zaprezentowaliśmy, w jaki sposób można efektywnie skorzystać z instrukcji MERGE, która pozwala jednocześnie wykonać operacje wstawiania, usuwania lub aktualizacji różnych wierszy, czyli łączy funkcje instrukcji INSERT, DELETE i UPDATE.
1. Dla celów demonstracyjnych posłużylismy się dwoma tablicami, z których jedna — #Grupy — przechowuje informacje o wszystkich ogólnopolskich grupach pasjonatów systemu SQL Server. Druga tablica — #GrupyZmiany — będzie przechowywała informacje o zmianach liczebności poszczególnych grup pasjonatów:
--TABELA ZAWIERAJACA NAZWY GRUP PASJONATOW SQL SERVER CREATE TABLE #Grupy ( [ID] [int] NOT NULL, [Nazwa] varchar(100) NOT NULL, [DataZalozenia] [datetime] NOT NULL, [Ilosc] [int] NOT NULL ) GO --TABELA ZAWIERAJACA MIESIECZNE RAPORTY --INFORMUJE ILE NOWYCH OSOB ZAPISALO SIE DO GRUP --(+/-) CREATE TABLE #GrupyZmiany ( [ID] [int] NOT NULL, [Nazwa] varchar(100) NOT NULL, [DataRaportu] [datetime] NOT NULL, [IleNowych] [int] NOT NULL ) GO
2. Obydwie tablice zostały wypełnione danymi testowymi:
INSERT INTO #Grupy VALUES (1,'KATOWICE','2008-01-01',100) ,(2,'WARSZAWA','2007-10-01',50) ,(3,'KRAKOW','2005-01-01',30) ,(4,'WROCLAW','2008-01-01',40) SELECT * FROM #Grupy GO --WSTAWIENIE INFORMACJI DO TABLICY PRZECHOWUJĄCEJ ZMIANY --PIERWSZY RAPORT INSERT INTO #GrupyZmiany VALUES (1,'KATOWICE','2008-01-07',5) ,(2,'WARSZAWA','2008-01-07',-20) ,(3,'KRAKOW','2008-01-07',10) ,(4,'WROCLAW','2008-01-07',-20) --DRUGI RAPORT INSERT INTO #GrupyZmiany VALUES (1,'KATOWICE','2008-02-07',45) ,(2,'WARSZAWA','2008-02-07',-30) ,(3,'KRAKOW','2008-02-07',20) ,(4,'WROCLAW','2008-02-07',-20) ,(5,'SZCZECIN','2008-02-07',130) SELECT * FROM #GrupyZmiany GO
3. Należy stworzyć takie zapytanie, które będzie aktualizowało tablicę #Grupy wykorzystując informacje zawarte w tablicy #GrupyZmiany w następujący sposób:
a. Jeżeli w tablicy #GrupyZmiany istnieje identyfikator grupy pasjonackiej, który nie istnieje w tablicy #Grupy, to musi on zostać do niej dodany,
b. Jeżeli w tablicy #GrupyZmiany istnieje identyfikator grupy pasjonackiej, który istnieje także w tablicy #Grupy, to informacja o liczbie członków grupy musi zostać zaktualizowana. W sytuacji, gdy po aktualizacji liczba członków w grupie będzie wynosiła 0, to grupa musi zostać usunięta z tablicy #Grupy
4. Przykład zaprezentowany poniżej pokazuje, w jaki sposób można osiągnąć zamierzony cel wykorzystując instrukcje DML znane w starszych wersjach systemu:
--aktualizacja UPDATE g SET g.Ilosc = g.Ilosc+gz. [Ile_nowych] FROM #Grupy g JOIN ( SELECT ID,Nazwa, SUM(IleNowych) as [Ile_nowych] FROM #GrupyZmiany GROUP BY ID ,Nazwa ) gz ON g.ID = gz.ID --wstawienie grupy INSERT INTO #Grupy(ID,Nazwa,DataZalozenia,Ilosc) SELECT ID,Nazwa,GETDATE(), SUM(IleNowych) FROM #GrupyZmiany gz WHERE NOT EXISTS (SELECT * FROM #Grupy g WHERE g.ID=gz.ID) GROUP BY ID ,Nazwa -- jezeli grupa nie ma aktywnych czlonkow, to nalezy ja usunac DELETE FROM #Grupy WHERE Ilosc = 0 GO
Takie rozwiązanie jest mało wydajne. Serwer bazodanowy trzykrotnie musi odwoływać się do tablicy#Grupy.
5. Na poniższym przykładzie pokazaliśmy, jak osiągnąć cel wykorzystując instrukcję MERGE:
MERGE #Grupy g USING ( SELECT ID,Nazwa, SUM(IleNowych) as [Ile_nowych] FROM #GrupyZmiany GROUP BY ID ,Nazwa ) gz ON g.ID = gz.ID WHEN MATCHED AND g.Ilosc + gz. [Ile_nowych] = 0 THEN DELETE WHEN MATCHED THEN UPDATE SET g.Ilosc += gz. [Ile_nowych] WHEN NOT MATCHED BY TARGET THEN INSERT (ID,Nazwa,DataZalozenia,Ilosc) VALUES (gz.ID,gz.Nazwa,GETDATE(),gz. [Ile_nowych]) ;
Omówienie:
Linie:
MERGE #Grupy g USING ( SELECT ID,Nazwa, SUM(IleNowych) as [Ile_nowych] FROM #GrupyZmiany GROUP BY ID ,Nazwa ) gz ON g.ID = gz.ID
pozwalają określić warunek złączenia tabel.
Kolejne linie pozwalają określić akcje, które mają zostać wykonane:
a) WHEN MATCHED - pozwala określić, co stanie się z rekordami, które znajdują się w obydwu tablicach
WHEN MATCHED AND g.Ilosc + gz. [Ile_nowych] = 0 THEN DELETE
oraz
WHEN MATCHED THEN UPDATE SET g.Ilosc += gz. [Ile_nowych]
b) WHEN NOT MATCHED BY TARGET - pozwala określić, co powinno sie wydarzyć, jeżeli nie ma odpowiedniego rekordu w tabeli docelowej
WHEN NOT MATCHED BY TARGET THEN INSERT (ID,Nazwa,DataZalozenia,Ilosc) VALUES (gz.ID,gz.Nazwa,GETDATE(),gz. [Ile_nowych])
Uwaga. Instrukcja MERGE musi zostać zakończona średnikiem (;).
Pełna składnia instrukcji MERGE znajduje się w pliku pomocy BOL.
6. W omawianym przypadku znacznie zwiększyła się wydajność zapytania w przypadku użycia instrukcji MERGE - ponad trzykrotnie. Pozostawiamy czytelnikowi samodzielne sprawdzenie tego faktu analizując plany zapytań.
Nowości w T-SQL – referencje
Dodatkowe informacje na temat funkcjonalności TDE można znaleźć w Internecie:
[1] Witryna SQL Server 2008 Jumpstart
[2] Kurs T-SQL na portalu www.wss.pl
[3] Książka "Praktyczny kurs SQL" autorstwa Marcina Szeligi, wydana przez wydawnictwo HELION
[4] Plik pomocy BOL
 | Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) |