Nowości w SQL Server 2012 – stronicowanie wyników (OFFSET, FETCH)

2012-04-22

W SQL Server aż do najnowszej wersji brakowało mechanizmu pozwalającego na stronicowanie wyników zapytań. Programiści radzili sobie z tym problemem wykorzystując dostępne konstrukcje (klauzula TOP, funkcja RowNumber(), tabela tymczasowa lub CTE) ale nie było gotowego rozwiązania. W najnowszej wersji języka T-SQL pojawiły się polecenia OFFSET i FETCH, które umożliwiają wygodne stronicowanie danych zwracanych przez zapytanie. Powyższe polecenia umieszczane są po klauzuli ORDER BY, która w ich przypadku jest wymagana. Składnia:

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

offset_value – ilość rekordów do pominięcia
fetch_value – ilość rekordów do pobrania

Kilka uwag:

  1. Wartości dla OFFSET i FETCH  możemy podawać jako liczba, zmienna typu int lub zapytanie zwracające wartość typu int;
  2. Słów kluczowych ROW i ROWS możemy używać zamiennie, to samo dotyczy słów FIRST i NEXT;
  3. Polecenie FETCH jest opcjonalne, bez niego zwrócone zostaną rekordy od wartości OFFSET + 1 do końca zbioru;

Żeby zobaczyć działanie omawianych poleceń musimy przygotować dane testowe:

--Utworzenie tabeli tymczasowej
create table #Test(Id int Identity(101, 1), Name varchar(20))

--Wstawienie 12 wierszy ("go 12" pozwala w Management Studio wykonać daną instrukcję dowolną ilość razy, w tym przypadku 12)
insert into #Test(Name)
select 'Name_' + Cast(DatePart(ms, GetDate()) as varchar(3))
go 12

select * from #Test

Oto zawartość tabeli #Test:

Poniższe zapytanie pomija pierwsze 6 rekordów (OFFSET 6 ROWS) i pobiera kolejne 4 rekordy (FETCH NEXT 4 ROWS ONLY):

select Id, Name
from #Test
order by Id
offset 6 rows
fetch next 4 rows only

Oto wynik (pominięte rekordy o Id 101-106 i pobrane rekordy o Id 107-110):

Oczywiście bezpośrednie podawanie wartości OFFSET i FETCH jest mało elastyczne, poniżej przykład prostego mechanizmu stronicowania w oparciu o zmienne:

--Numer strony do pobrania
declare @PageNumber int = 2
--Ilość rekordów na stronie
declare @PageSize int = 3

select Id, Name
from #Test
order by Id
offset ((@PageNumber - 1) * @PageSize) rows
fetch next @PageSize rows only

Oto wyniki, pobrana druga strona przy trzech rekordach na stronie:

Więcej informacji o nowościach w SQL Server 2012: MSDN 

Reklamy

Posted on 2012-04-22, in SQL Server and tagged , , . Bookmark the permalink. Dodaj komentarz.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: