Stránkování je proces, který se používá k rozdělení velkého objemu dat na menší samostatné stránky a tento proces je také znám jako stránkování. Stránkování se běžně používá ve webových aplikacích a lze se s ním setkat na stránkách Google. Když na Googlu něco hledáme, zobrazí se výsledky na oddělené stránce; to je hlavní myšlenka stránkování.
Nyní si v dalších částech článku probereme, jak dosáhnout stránkování v SQL Serveru.
Příprava vzorových dat
Než se začneme stránkováním podrobně zabývat, vytvoříme vzorovou tabulku a naplníme ji syntetickými daty. V následujícím dotazu vytvoříme tabulku SampleFruits, která bude uchovávat názvy a prodejní ceny ovoce. V další části článku budeme tuto tabulku používat.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE SampleFruits (
Id INT PRIMARY KEY IDENTITY(1,1) ,
FruitName VARCHAR(50) ,
Price INT
)
GO
INSERT INTO SampleFruits VALUES(„Apple“,20)
INSERT INTO SampleFruits VALUES(‚Apricot‘,12)
INSERT INTO SampleFruits VALUES(‚Banana‘,8)
INSERT INTO SampleFruits VALUES(‚Cherry‘,11)
INSERT INTO SampleFruits VALUES(‚Strawberry‘,26)
INSERT INTO SampleFruits VALUES(‚Lemon‘,4)
INSERT INTO SampleFruits VALUES(‚Kiwi‘,14)
INSERT INTO SampleFruits VALUES(‚Coconut‘,34)
INSERT INTO SampleFruits VALUES(‚Orange‘,24)
INSERT INTO SampleFruits VALUES(‚Raspberry‘,13)
INSERT INTO SampleFruits VALUES(‚Mango‘,9)
INSERT INTO SampleFruits VALUES(‚Mandarin‘,19)
INSERT INTO SampleFruits VALUES(‚Ananas‘,22)
GO
SELECT * FROM SampleFruits
|
Co je stránkování v SQL Serveru?
Z hlediska SQL Serveru je cílem stránkování, rozdělení sady výsledků na samostatné stránky pomocí dotazu. Pokud jsou v příkazu SELECT použity argumenty OFFSET a FETCH spolu s klauzulí ORDER BY, jedná se o řešení stránkování v SQL Serveru.
Argument OFFSET určuje, kolik řádků bude z množiny výsledků dotazu vynecháno. V následujícím příkladu dotaz přeskočí první 3 řádky tabulky SampleFruits a poté vrátí všechny zbývající řádky.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Když nastavíme hodnotu OFFSET na 0, nebudou ze souboru výsledků vynechány žádné řádky. Příkladem tohoto typu použití může být následující dotaz:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Na druhou stranu, pokud nastavíme hodnotu OFFSET, která je větší než celkový počet řádků souboru výsledků, nezobrazí se ve výsledku žádný řádek. Uvažujeme-li následující dotaz, celkový počet řádků tabulky SampleFruits je 13 a my jsme nastavili hodnotu OFFSET na 20, takže dotaz nezobrazí žádný výsledek.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ŘÁDKŮ
|
Argument FETCH určuje, kolik řádků bude zobrazeno ve výsledku, a argument FETCH musí být použit s argumentem OFFSET. V následujícím příkladu vynecháme prvních 5 řádků a poté omezíme sadu výsledků na 6 řádků pro naši ukázkovou tabulku.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: KLASA TOP omezuje počet řádků vrácených z příkazu SELECT. Když použijeme klauzuli TOP bez ORDER BY, může být vrácen libovolný výsledek. Uvážíme-li následující příklad, vrátí při každém provedení dotazu 3 náhodné řádky.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Jak jsme se naučili, argument OFFSET-FETCH vyžaduje v příkazu SELECT klauzuli ORDER BY. Pokud chceme implementovat nedefinované pořadí, které má rádo předchozí použití klauzule TOP s argumenty OFFSET-FETCH, můžeme použít dotaz, který vypadá následovně:
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Dotaz na stránkování v SQL Serveru
Po zjištění odpovědi na otázku „Co je stránkování?“ se dozvíme, jak můžeme napsat stránkovací dotaz v SQL Serveru. Nejprve provedeme následující dotaz a budeme řešit dotaz:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Cena FROM SampleFruits
ORDER BY Cena
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Jak vidíme, ve výše uvedeném dotazu jsme deklarovali dvě proměnné, kterými jsou:
- @PageNumber – Určuje číslo stránky, která bude zobrazena
- @RowsOfPage – Určuje, kolik řádků bude na stránce zobrazeno. Výsledkem je, že příkaz SELECT zobrazí druhou stránku, která obsahuje 4 řádky
Dynamické řazení se stránkováním
Aplikace mohou kromě stránkování potřebovat seřadit data podle různých sloupců buď vzestupně, nebo sestupně. K překonání tohoto typu požadavku můžeme použít klauzuli ORDER BY s podmínkami CASE, takže získáme dotaz, který lze seřadit podle proměnných. Příkladem tohoto typu použití může být následující dotaz:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @SortingCol AS VARCHAR(100) =’FruitName‘
DECLARE @SortType AS VARCHAR(100) = ‚DESC‘
SET @PageNumber=1
SET @RowsOfPage=4
SELECT FruitName,Cena FROM SampleFruits
ORDER BY
CASE WHEN @SortingCol = ‚Cena‘ AND @SortType =’ASC‘ THEN Cena END ,
CASE WHEN @SortingCol = ‚Price‘ AND @SortType =’DESC‘ THEN Price END DESC,
CASE WHEN @SortingCol = ‚FruitName‘ AND @SortType =’ASC‘ THEN FruitName END ,
CASE WHEN @SortingCol = ‚FruitName‘ AND @SortType =’DESC‘ THEN FruitName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Also, můžeme změnit sloupec řazení a směr řazení prostřednictvím proměnných pro výše uvedený dotaz.
Stránkování ve smyčce
V tomto příkladu se naučíme techniku dotazování, která vrátí všechny výsledky diskrétních stránek pomocí jediného dotazu.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @MaxTablePage AS FLOAT
SET @PageNumber=1
SET @RowsOfPage=4
SELECT @MaxTablePage = COUNT(* FROM SampleFruits
SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
WHILE @MaxTablePage >= @PageNumber
BEGIN
SELECT FruitName,Cena FROM SampleFruits
ORDER BY Cena
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Pro tento dotaz, jsme vytvořili poměrně jednoduchý vzorec. Nejprve jsme do proměnné @MaxTablePage přiřadili celkový počet řádků tabulky SampleFruit a poté jsme jej rozdělili na to, kolik řádků se zobrazí na jedné stránce. Vypočítali jsme tedy počet stránek, které budou zobrazeny. Vypočtená hodnota však může být desetinné číslo, a proto jsme ji pomocí funkce CEILING zaokrouhlili na nejmenší celé číslo, které je větší než vypočtené číslo. V druhém kroku jsme implementovali WHILE-LOOP a iterovali proměnnou @PageNumber až do poslední stránky čísla.
Závěr
V tomto článku jsme se snažili najít odpověď na otázku „Co je stránkování?“, zejména pro SQL Server. Argumenty OFFSET-FETCH pomáhají implementovat, kolik řádků chceme vynechat a kolik řádků chceme zobrazit v souboru výsledků, pokud je použijeme s klauzulí ORDER BY v příkazech SELECT. A nakonec jsme se dozvěděli, jak můžeme pomocí těchto argumentů dosáhnout stránkování v SQL Serveru.
- Autor
- Poslední příspěvky
Většinu své kariéry se věnoval správě a vývoji databází SQL Server. V současné době se zabývá správou databází a Business Intelligence. Najdete ho na síti LinkedIn.
Zobrazit všechny příspěvky od Esat Erkec
- Mýty o optimalizaci dotazů – 23. března, 2021
- Příznaky sniffingu parametrů v SQL Serveru – 17. března 2021
- Použití automatické korekce plánu pro ladění dotazů – 4. března 2021