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;