Funkcje pozycjonujące

Najnowszy MS SQL Server 2005 doczekał się w końcu mechanizmu numerowania wierszy. Może to brzmi troszeczkę niezwykle, ale wszystkie poprzednie wersje serwera były pozbawione jakiegokolwiek mechanizmu numerowania wierszy w zbiorze wynikowym zapytania. Oczywiście zaimplementowanie takiego mechanizmu nie było zbyt wyrachowanym zadaniem, aczkolwiek wydaje mi się, iż tak podstawowa operacja powinna mieć standardowy mechanizm. Tak też się stało. Mamy dostęp teraz do czterech funkcji pozycjonujących których opisy zamieszczam w poniższej tabeli.

Teraz, aby wyświetlić kontakty do wszystkich osób posortowanie ze względu na nazwisko z przypisanym mu numerem wiersza wystarczy uruchomić następujący skrypt.

SELECT Row_Number() OVER (ORDER BY [LastName]) AS RowNumber
      ,[ContactID]
      ,[FirstName]
      ,[LastName]
FROM [Person].[Contact]

Funkcja Row_Number() zwraca numer wiersza, ale wymagane jest jeszcze określenie według jakich parametrów numer ma być nadawany. W tym celu dodajemy klauzulę OVER z kolumnami, po których chcemy, aby numer był wyliczany. Użycie klauzul numerujących jest jak widać proste, trudniejszą sprawą może być wytłumaczenie, na jakiej zasadzie działają poszczególne funkcje pozycjonujące. Aby ułatwić sobie zadanie posłużę się przykładem.

SELECT Row_Number() OVER (ORDER BY [LastName]) AS RowNumber
      ,Rank() OVER (ORDER BY [LastName]) AS Rank
      ,Dense_Rank() OVER (ORDER BY [LastName]) AS DenseRank
      ,NTile(3) OVER (ORDER BY [LastName]) AS NTile_3
      ,NTile(4) OVER (ORDER BY [LastName]) AS NTile_4
      ,[ContactID]
      ,[FirstName]
      ,[LastName]
FROM [Person].[Contact]

Pewnie niektórzy już myślą o dodaniu do powyższego kawałka kodu linii która wyglądać będzie mniej więcej następująco:

WHERE (RowNumber > 51) AND (RowNumber < 100)

i dzięki temu będzie miał rozwiązany problem o nazwie „stronicowanie wyników”. Niestety tak jednak się nie stanie. Klauzula WHERE determinuje te wiersze, które mają zostać zaznaczone, a same funkcje pozycjonujące działają na gotowych wynikach. Aby uzyskać skrypt działający zgodnie z naszymi oczekiwaniami moglibyśmy powyższe zapytanie zrobić zapytaniem wewnętrznym i dopiero w zewnętrznym zapytaniu użyć klauzuli WHERE. Moglibyśmy, ale możemy również użyć wyrażeń tablicowych.

Wyrażenia tablicowe (CTE)

Wyrażenia tablicowe, jak wcześniej wspomniałem, są mechanizmem, którego nie było w poprzedniej wersji serwera. Umożliwiają definiowanie wirtualnych widoków, których można użyć w następnym skrypcie. Sama składnia polecenia CTE jest nie skomplikowana.

WITH nazwa_wirtualnej_tabeli
AS
(
            zapytanie
)

Po wykonaniu tego zapytania mamy już dostępną wirtualną tabelę na której możemy swobodnie przeprowadzać operacje. Powróćmy więc do utworzenia skryptu, który wybierałby wiersze od 51 – 100 z tabeli Contact przy użyciu CTE.

WITH tblContact
AS
(
           SELECT Row_Number() OVER (ORDER BY [LastName]) AS RowNumber
                        ,Rank() OVER (ORDER BY [LastName]) AS Rank
                        ,Dense_Rank() OVER (ORDER BY [LastName]) AS DenseRank
                        ,NTile(3) OVER (ORDER BY [LastName]) AS NTile_3
                        ,NTile(4) OVER (ORDER BY [LastName]) AS NTile_4
                        ,[ContactID]
                        ,[FirstName]
                        ,[LastName]
           FROM [AdventureWorks].[Person].[Contact]
)
SELECT *
FROM tblContact
WHERE (RowNumber > 50) AND (RowNumber <= 100)

Najpierw utworzyliśmy wirtualną tabelę tblContact zawierającą wszystkie dane, które zostały zwrócone w wewnętrznym zapytaniu, łącznie z numerami wierszy wyliczonymi przez funkcje pozycjonujące. Teraz możemy wybrać z wirtualnej tabeli tą stronę danych, której potrzebujemy.

Rekurencja

Za pomocą wyrażeń tabelarycznych możemy zrealizować inną bardzo ciekawą funkcjonalność – rekurencje. Rekurencja we wcześniejszym serwerze nie była wspomagana w żaden sposób. Oczywiście nie ma przeszkody której nie dałoby się obejść. Programiści wypracowali parę sposobów na tworzenie własnych zapytań rekurencyjnych. Teraz sytuacja znacznie się poprawiła. Wprowadzenie wyrażeń tablicowych umożliwia złączenie wyników zapytania wewnętrznego z wyrażeniem CTE, które je zawiera. Ten wyraźny sygnał to zielone światło dla rekurencji.

Poniższy przykład wybiera z tabeli Employee pracowników i przypisanych im przełożonych.

WITH tblManager AS
(
    SELECT EmployeeID
           , LoginID
           , ManagerID
           , CAST(NULL as nvarchar(100)) AS MgrLogin
    FROM   HumanResources.Employee
           WHERE ManagerID IS NULL
           UNION ALL
    SELECT emp.EmployeeID
           , emp.LoginID
           , emp.ManagerID
           , CAST(mgr.LoginID as nvarchar(100))
           FROM HumanResources.Employee emp INNER JOIN tblManager mgr
           ON emp.ManagerID = mgr.EmployeeID
)

SELECT *
FROM tblManager
ORDER BY ManagerID

Prześledźmy krok po kroku, co tak naprawdę dzieje się w powyższym zapytaniu. Pierwszy krok to zainicjalizowanie wartości początkowej. Wybieramy prezesa firmy. Czyli osobę która nie ma przełożonego. W tym celu wpisujemy następujący skrypt.

SELECT  EmployeeID
        , LoginID
        , ManagerID
        , CAST(NULL as nvarchar(100)) AS MgrLogin
FROM HumanResources.Employee

Zwraca on osobę o loginie adventure-works\ken0  i EmployeeID równym 109. Następnym krokiem jest złączenie wyników, które już znajdują się w tabeli CTE z Employee z tym, że złączenie dokonujemy po następujących polach:

emp.ManagerID = mgr.EmployeeID

Oznacza to, że dla wszystkich pracowników, których mamy już wstawionych w tabeli wirtualnej, wybieramy ich podwładnych z tabeli Employee. A następnie dla wyników tego zapytania wybieramy pracowników, którzy będą z kolei przełożonymi kolejnych itd.
W naszym konkretnym przypadku wybierzemy ludzi, których szefem jest pan z EmployeeID, a następnie dla nich wybierzemy ich podwładnych itd.

Podsumowanie

Z całej gamy usprawnień, jakie posiada najnowszy SQL Server 2005 w stosunku do poprzednika przedstawiłem te, które moim zdaniem są najbardziej użytecznie i których prędzej czy później będzie trzeba się nauczyć. O ile funkcje pozycjonujące to bardziej ciekawostka, która tylko lekko usprawnia pracę, to wprowadzenie wyrażeń tablicowych znacznie poszerza możliwości rozwoju własnych aplikacji. Jak zademonstrowałem na przykładzie prostego skryptu rekurencyjnego, którego implementacja za pomocą standardowych zabiegów byłaby skomplikowanym zagadnieniem.