Denali CTP 1 – co nowego nie tylko dla deweloperów – cz. 4

Autor: Damian Widera, Łukasz Grala

Opublikowano: 2011-01-28

Deweloperskich nowości ciąg dalszy

Prezentujemy kolejną porcję informacji dotyczących nowości dostępnych w SQL Server „Denali” CTP1. Pokazane zostaną sposoby korzystania z paginacji za pomocą opcji OFFSET oraz FETCH.

Opcje OFFSET oraz FETCH

SQL Server 2008 R2 (oraz wcześniejsze wersje, z SQL Server 2005 włącznie) oferowały możliwość paginacji (czyli stronicowania) wyników zapytania przy użyciu funkcji ROW_NUMBER oraz opcji TOP.

Opcja TOP pozwalała na ograniczenie liczby zwracanych wyników (np. do 10 wierszy) , ale nie pozwalała na opuszczanie wyników zapytania, np. zwrócić 10 wyników, ale pierwsze 10 opuścić. Warto przy tej okazji przypomnieć, że opcja TOP nie należy do standardu ANSI i nie jest spotykana w innych relacyjnych silnikach baz danych.

Funkcja ROW_NUMBER pozwala zwrócić dokładny zakres wierszy, np. wiersze o numerach 11–20. Ale głównym ograniczeniem jest jednak niemożność jej użycia w klauzuli WHERE. Można ten problem obejść, wykorzystując CTE:

;WITH _cte AS (       SELECT             ROW_NUMBER() OVER(ORDER BY DepartmentID) rn,             DepartmentID,             Name,             GroupName       FROM HumanResources.Department ) SELECT * FROM _cte WHERE rn BETWEEN 6 AND 15

Opcje OFFSET oraz FETCH pozwalają na rozwiązanie omówionych wcześniej problemów. Obydwie opcje mogą być potraktowane jako rozszerzenie klauzuli ORDER BY ich użyć ich można w następujący sposób:

1. Bezpośrednio po kolumnach zawartych w klauzuli ORDER BY należy użyć opcji OFFSET, która jest obowiązkowa. Opcja ta określa ilość wierszy uzyskanych z wyniku zapytania, które powinny zostać opuszczone.
2. Po opcji OFFSET można użyć opcji FETCH FIRST lub FETCH NEXT, podając liczbę wierszy, która ma zostać zwrócona do aplikacji klienckiej.

Przykładem, który realizuje zapytanie przedstawione powyżej, a w którym wykorzystano nową składnię, jest poniższy kod:

SELECT       DepartmentID,       Name,       GroupName FROM HumanResources.Department ORDER BY DepartmentID OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

Opcje OFFSET oraz FETCH mogą przyjmować wartości podanych parametrów, co w praktyce umożliwia dynamiczne stronicowanie wyników. Użycie nowych opcji w tym przypadku jest analogiczne do użycia funkcji ROW_NUMBER, która w połączeniu z CTE może być sparametryzowana w podobny sposób. Poniżej znajduje się kod, w którym pokazano, jak wykorzystać parametry do zainicjowania opcji OFFSET oraz FETCH.

DECLARE @Start tinyint = 1       , @LiczbaWierszy tinyint = 8; SELECT       DepartmentID,       Name,       GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @Start ROWS FETCH NEXT @LiczbaWierszy ROWS ONLY;

Jedną  z ciekawszych możliwości, którą można się posłużyć przy wykorzystaniu omawianych opcji OFFSET oraz FETCH, jest możliwość utworzenia tabela, z której dane będą czytane do wspomnianych opcji. Koncepcja ta została zaprezentowana na poniższych przykładach. W pierwszym z nich tworzona jest tabela AppSettings, do której wstawiany jest wiersz zawierający informację o ilości wyświetlanych wyników (w kolumnie PageSize).

USE AdventureWorks2008R2; GO CREATE TABLE dbo.AppSettings (       AppSettingID int,       PageSize int ); GO INSERT INTO dbo.AppSettings VALUES(1, 10); GO

Utworzona w ten sposób tabela może zostać wykorzystana w zapytaniu jako źródło danych dla nowych opcji:

DECLARE @Start tinyint = 1; SELECT       DepartmentID,       Name,       GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @Start ROWS FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY; GO

Na zakończenie warto zwrócić uwagę, że nie można użyć opcji OFFSET nie wskazując wcześniej w klauzuli ORDER BY sposobu na posortowanie wyniku zapytania. Jest to inna sytuacja, niż w przypadku korzystania z opcji TOP. Próba wykonania poniższego zapytania zakończy się więc błędem:

SELECT DepartmentID, Name, GroupName FROM HumanResources.Department OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; ----------- Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '0'. Msg 153, Level 15, State 2, Line 4 Invalid usage of the option NEXT in the FETCH statement.

Można jednak używać opcji OFFSET z wartością 0, co oznacza, że w wyniku zapytania nie zostanie pominięty żaden wiersz.