Akademia SQL - Część 11: Typy i parametry tabelaryczne     Akademia SQL     Akademia SQL - Część 13: Konsola PowerShell

Akademia SQL - Część 12: Nowości w T-SQL Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 4 listopada 2008

Zawartość strony
 Nowości w T-SQL – informacje podstawowe   Nowości w T-SQL – informacje podstawowe
 Nowości w T-SQL – laboratorium   Nowości w T-SQL – laboratorium
 Nowości w T-SQL – referencje   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.

 Do początku strony Do początku strony

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ń.

 Do początku strony Do początku strony

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)
Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”.

Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.
 Do początku strony Do początku strony

Akademia SQL - Część 11: Typy i parametry tabelaryczne     Akademia SQL     Akademia SQL - Część 13: Konsola PowerShell