SQL Server 2012 - Nowe opcje stronicowania wyników zapytań  

Autor: Damian Widera

Opublikowano: 2012-11-28

SQL Server 2012 umożliwia programistom uzyskanie efektywnego mechanizmu stronicowania wyników zapytania. Programiści używający wcześniejszej wersji SQL Server korzystali z innych rozwiązań języka TSQL, które pozwalały uzyskać podobny efekt. Najczęściej były to konstrukcje wykorzystujące tabele tymczasowe lub CTE, w powiązaniu z funkcją ROW_NUMBER.

Poniższy przykład obrazuje jedną z wcześniejszych metod stronnicowania wyników zapytania. Wykorzystano w niej funkcję ROW_NUMBER oraz dwie zmienne odpowiedzialne za wyznaczenie przedziału, z którego powinny zostać zwrócone wyniki:

DECLARE @start int = 5;
DECLARE @end int = 12;

SELECT object_id, name 
FROM (
        SELECT
            [object_id], 
            name,
            ROW_NUMBER() OVER(Order by name) R
        FROM sys.all_objects
)X
WHERE R BETWEEN @start AND @end
GO

W SQL Server 2012 można skorzystać z poleceń OFFSET oraz FETCH, które pozwalają wykonać stronnicowanie bez konieczności użycia innych poleceń TSQL. Obydwie frazy muszą zostać umieszczone po poleceniu ORDER BY:

OFFSET offset_value { ROW | ROWS } 
[FETCH { FIRST | NEXT } fetch_value { ROW | ROWS } ONLY]

Polecenie OFFSET posiada parametr offset_value, wyznaczający ilość wierszy, które powinny zostać pominięte (przeskoczone). Opcjonalne polecenie FETCH posiada parametr fetch_value, wskazujący ilość wierszy, która zostanie zwrócona do aplikacji klienckiej.

Poniższy przykład pokazuje sposób użycia nowej składni, związanej z poleceniem ORDER BY:

SELECT
    [object_id], 
    name
FROM sys.all_objects
ORDER BY name
OFFSET  10 ROWS
FETCH NEXT 5 ROWS ONLY;

W podanym przykładzie zostaną wyświetlone wiersze począwszy od numeru 11 do 15.

Parametry poleceń OFFSET oraz FETCH mogą być zarówno zmiennymi, jak i zostać użyte w analogiczny sposób, jak w poprzedniej wersji SQL Server:

DECLARE @StartingRowNumber tinyint = 1
      , @FetchRows tinyint = 8;
SELECT
    [object_id], 
    name
FROM sys.all_objects
ORDER BY name
OFFSET  @StartingRowNumber  ROWS
FETCH NEXT @FetchRows  ROWS ONLY

Jedną z ciekawszych opcji jest możliwość użycia danych, przechowywanych w tabeli do sterowania liczbą zwracanych wierszy. W poniższym przykładzie pokazano, jak odczytać informacje z tabeli konfiguracyjnej AppSettings oraz w jaki sposób wykorzystać je jako parametr polecenia FETCH:

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

DECLARE @StartingRowNumber tinyint = 1;

SELECT
    [object_id], 
    name
FROM sys.all_objects
ORDER BY name
    OFFSET @StartingRowNumber ROWS 
    FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;