Nie taki znowu zwykły SQL Server cz. 1 – słów kilka o CTE

Niniejszym artykułem chciałbym rozpocząć cykl poświęcony przydatnym, ale rzadziej wykorzystywanym elementom języka SQL jak i samego MS SQL Server’a. Podstawowe operacje CRUD wykonujemy bez większych trudności już po krótkim szkoleniu, jednak niejednokrotnie brakuje narzędzia upraszczającego życie oraz lepiej pasującego do aktualnych okoliczności.


Aby nie sprowadzać kolejnych artykułów do suchej wiedzy w miarę możliwości będę starał się nadawać im formę krótkich tutoriali, wraz z rzeczywistymi przypadkami użycia, na które można natrafić w codziennej pracy.

Środowisko

Poniższy tekst będzie zawierał kilka przemyśleń na temat środowiska na jakim przeprowadziłem dalsze operacje. Śmiało możesz przejść dalej 🙂 

Przykłady będę uruchamiał na MS SQL Server – jest to najczęściej używana przeze mnie zawodowo baza danych, co więcej jej popularność na rynku rośnie z każdym rokiem. Jako, że prywatnie używam Linux’a myślę, że całkiem fajnym eksperymentem byłoby przetestowanie wersji Preview bazy. Zasadniczo wybór środowiska nie powinien mieć znaczenia.

Jeżeli chcecie podążyć moją ścieżką sugeruję instalację za pomocą dockera:

Ważna uwaga – stosujcie się do wymogów dot. hasła w przeciwnym wypadku może być tak, że kontener po prostu nie wystartuje i po kilku sekundach zobaczymy, że się po prostu wyłączył. Do weryfikacji, czy wszystko chodzi ok można użyć:

Niestety MS nie przygotował (zapewne jeszcze, choć nie śledzę wieści ze społeczności) Management Studio pod Linux’a, póki co zalecają korzystanie z Visual Studio Code z zainstalowaną wtyczką o jakże oryginalnej nazwie mssql for Visual Studio Code.

CTE – niezbędne minimum wiedzy

CTE, które można przetłumaczyć na język polski jako wspólne wyrażenia tablicowe stanowią element standardu języka SQL wprowadzony już w 1999 roku, przede wszystkim ze względu na konieczność obsługi modeli hierarchicznych. CTE łatwo dostrzec wśrod zapytań po słowie kluczowym WITH i należy o nich myśleć, bez zbędnego wchodzenia w szczegóły jako o zapytaniach budujących tymczasową pośrednią tabelę w pamięci z „podzapytań”. Stop, stop ktoś mógłby zakrzyknąć, ale czy nie mamy już podzapytań działających tak samo ?
Otóż, i tak i nie – specjalną cechą CTE, jest możliwość obsługi rekurencji, ale do tego dojdziemy… w swoim czasie.

Jak wygląda zatem typowe wyrażenie tablicowe ? Najczęściej piszemy coś podobnego do kodu poniżej:

Warto zwrócić uwagę, że definicja kolumn nie jest tak naprawdę potrzebna, o czym przekonamy się już niedługo. Przechodząc szybciutko do praktyki.

Przypadek użycia nr 1 – usuwanie zduplikowanych wersji w przyjemny sposób

Jak widać troszkę omsknęła nam się dłoń podczas wstawiania danych i mamy dwa razy więcej wierszy niż byśmy chcieli:

Co poradzić w takiej sytuacji ? Intuicja podpowiada nam, że musimy znaleźć wiersze które chcemy usunąć i… je usunąć. Gdybyśmy np. ponumerowali powtarzające się wiersze i zostawili sobie tylko jeden, wtedy wszystko powinno być ok. Użyjmy zatem funkcji okienkowej, rozdzielającej numerację względem wszystkich kolumn poza PK.

Jak widać na obrazku jest już znacznie lepiej, teraz tylko wystarczy pozostawić wiersz o RN=1 i  na końcu odpalić DELETE.

Niestety nie ma tak lekko… funkcja okienkowa zmusza nas do użycia podzapytania. Czy nie wspominałem, że CTE są bardzo zbliżone do podzapytań ? Nie uprzedzając faktów:

Czyż nie o to nam chodziło teraz tylko usuwać!

 

Ale jak to 10 rows affected ?! Cóż wygląda na to że pośpiech jest dobry przy łapaniu pcheł i wyczyściliśmy sobie całą tabelkę. Załadujmy wiersze jeszcze raz i… czy potraficie dostrzec pomyłkę ?

Okej okej, ale gdzie miejsce dla CTE w tym wszystkim, ktoś zapyta? Jak już wspominałem można je użyć jako podzapytanie.

Dokładnie ten sam wynik, prosto, szybko i przyjemnie

A teraz pomyślmy jakby tu usunąć te rekordy ? Wystarczy SELECT * zamienić na DELETE.

5 rows affected czy nie o to nam właśnie chodziło? Sami przyznajcie czy ta droga nie jest o wiele łatwiejsza ? Moim zdaniem sam sposób dekompozycji problemu na samym początku na mniejsze podzapytania, jest najlepszym podejściem jakie może być. Odnosimy wrażenie, że operujemy na prawdziwej tabeli istniejącej gdzieś w bazie, a nie na zwykłym CTE’ku.

Nasz wpis zrobił się strasznie długi, a w temacie CTE do powiedzenia zostało jeszcze trochę. Prawdziwą potęgę wyrażeń tablicowych zostawię do następnego odcinka naszego cyklu.

Proszę miejcie na uwadze jedno. CTE nie są „złotym młotkiem”, nie ma czegoś takiego w IT, zatem jeśli macie zamiar przepisywać wszystkie wasze wyrażenia SQL z podzapytaniami, zastanówcie się czy na pewno warto. W najprostszych przypadkach nie uzyskacie raczej poprawy czytelności, a co do wydajności – cóż też często jest zbliżona do podzapytań, myślę że każdy przypadek wymagałby osobnej weryfikacji.
Jedno jest pewne w tych najbardziej zakręconych zapytaniach na pewno nie zgubicie się tworząc kilka logicznych CTE’ków, które następnie odpowiednio połączycie.

Tyle na dziś. Dzięki wszystkim którzy dotarli aż tutaj 🙂 Do następnego razu!

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *