Paginarea este un proces care este utilizat pentru a împărți date mari în pagini discrete mai mici, iar acest proces este cunoscut și sub numele de paginare. Paginarea este utilizată în mod obișnuit de aplicațiile web și poate fi observată pe Google. Atunci când căutăm ceva pe Google, acesta afișează rezultatele pe pagina separată; aceasta este ideea principală a paginării.
Acum, vom discuta despre cum să realizăm paginarea în SQL Server în următoarele părți ale articolului.
Pregătirea datelor de probă
Înainte de a începe să intrăm în detalii despre paginare, vom crea un tabel de probă și îl vom popula cu câteva date sintetice. În următoarea interogare, vom crea un tabel SampleFruits care stochează numele fructelor și prețurile de vânzare. În următoarea parte a articolului, vom utiliza acest tabel.
|
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(‘Pineapple’,22)
GO
SELECT * FROM SampleFruits
|
Ce este paginarea în SQL Server?
În ceea ce privește SQL Server, scopul paginării este, împărțirea unui set de rezultate în pagini discrete cu ajutorul interogării. Atunci când argumentele OFFSET și FETCH sunt utilizate împreună cu clauza ORDER BY într-o instrucțiune SELECT, aceasta va fi o soluție de paginare pentru SQL Server.
Argumentul OFFSET specifică câte rânduri vor fi sărite din setul de rezultate al interogării. În exemplul următor, interogarea va sări peste primele 3 rânduri din tabelul SampleFruits și apoi va returna toate rândurile rămase.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Atunci când se stabilește valoarea OFFSET ca fiind 0, niciun rând nu va fi sărit din setul de rezultate. Următoarea interogare poate fi un exemplu al acestui tip de utilizare:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Pe de altă parte, dacă setăm valoarea OFFSET, care este mai mare decât numărul total de rânduri din setul de rezultate, nu se va afișa niciun rând în rezultat. Dacă luăm în considerare următoarea interogare, numărul total de rânduri din tabelul SampleFruits este de 13, iar noi am stabilit valoarea OFFSET la 20, astfel încât interogarea nu va afișa niciun rezultat.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
Argumentul FETCH specifică câte rânduri vor fi afișate în rezultat, iar argumentul FETCH trebuie să fie utilizat împreună cu argumentul OFFSET. În exemplul următor, vom sări peste primele 5 rânduri și apoi vom limita setul de rezultate la 6 rânduri pentru tabelul nostru de probă.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: Clauza TOP CLAUSE limitează numărul de rânduri care se întorc din instrucțiunea SELECT. Atunci când folosim clauza TOP fără ORDER BY, se poate reveni la rezultate arbitrare. Dacă luăm în considerare următorul exemplu, acesta va returna 3 rânduri aleatorii la fiecare execuție a interogării.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
După cum am învățat, argumentul OFFSET-FETCH necesită clauza ORDER BY în instrucțiunea SELECT. Dacă dorim să implementăm o ordine nedefinită care seamănă cu utilizarea anterioară a clauzei TOP cu argumentele OFFSET-FETCH, putem utiliza o interogare care arată ca mai jos:
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
.
Interogare de paginare în SQL Server
După ce am aflat răspunsul la întrebarea „Ce este paginarea?” întrebare, vom învăța cum putem scrie o interogare de paginare în SQL Server. La început, vom executa următoarea interogare și vom aborda interogarea:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
După cum putem vedea, am declarat două variabile în interogarea de mai sus, iar aceste variabile sunt:
- @PageNumber – Precizează numărul paginii care va fi afișată
- @RowsOfPage – Precizează câte rânduri vor fi afișate pe pagină. Ca rezultat, instrucțiunea SELECT afișează a doua pagină, care conține 4 rânduri
Sortare dinamică cu paginare
Este posibil ca aplicațiile să aibă nevoie să sorteze datele în funcție de diferite coloane, fie în ordine crescătoare, fie în ordine descrescătoare, alături de paginare. Pentru a depăși acest tip de cerințe, putem utiliza o clauză ORDER BY cu condiții CASE, astfel încât să obținem o interogare care poate fi sortată în funcție de variabile. Următoarea interogare poate fi un exemplu de acest tip de utilizare:
|
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,Price FROM SampleFruits
ORDER BY
CASE WHEN @SortingCol = ‘Price’ AND @SortType =’ASC’ THEN Price 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
|
De asemenea, putem schimba coloana de sortare și direcția de sortare prin intermediul variabilelor pentru interogarea de mai sus.
Paginare într-o buclă
În acest exemplu, vom învăța o tehnică de interogare care returnează toate rezultatele paginilor discrete cu o singură interogare.
|
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,Price FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Pentru această interogare, am creat o formulă destul de simplă. La început, am atribuit numărul total de rânduri din tabelul SampleFruit la variabila @MaxTablePage, iar apoi l-am împărțit în câte rânduri vor fi afișate pe o pagină. Astfel, am calculat numărul de pagini care vor fi afișate. Cu toate acestea, valoarea calculată poate fi o zecimală și, pentru aceasta, am folosit funcția CEILING pentru a o rotunji la cel mai mic număr întreg care este mai mare decât numărul calculat. Ca un al doilea pas, am implementat un WHILE-LOOP și am iterat variabila @PageNumber până la ultima pagină a numărului.
Concluzie
În acest articol, am încercat să aflăm răspunsul la întrebarea „Ce este paginarea?”, în special pentru SQL Server. Argumentele OFFSET-FETCH ajută la implementarea numărului de rânduri pe care dorim să le sărim și a numărului de rânduri pe care dorim să le afișăm în setul de rezultate atunci când le folosim cu clauza ORDER BY în instrucțiunile SELECT. Și, în final, am învățat cum putem realiza paginarea în SQL Server cu aceste argumente.
- Autor
- Recent Posts
Cea mai mare parte a carierei sale a fost axată pe administrarea și dezvoltarea bazelor de date SQL Server. Interesele sale actuale sunt în administrarea bazelor de date și Business Intelligence. Îl puteți găsi pe LinkedIn.
Vezi toate postările lui Esat Erkec
- Query Optimization Myths – 23 martie, 2021
- Symptoms of Parameter Sniffing in SQL Server – 17 martie 2021
- Using Automatic Plan Correction for Query Tuning – 4 martie 2021