Pagineren is een proces dat wordt gebruikt om een grote hoeveelheid gegevens op te delen in kleinere discrete pagina’s, en dit proces is ook bekend als paging. Paginering wordt vaak gebruikt door webapplicaties en is te zien op Google. Wanneer we naar iets zoeken op Google, toont het de resultaten op de afzonderlijke pagina; dit is het belangrijkste idee van de paginering.
Nu, zullen we bespreken hoe te bereiken paginering in SQL Server in de volgende delen van het artikel.
Voorbereiding van voorbeeldgegevens
Voordat we in detail ingaan op de paginering, zullen we een voorbeeldtabel maken en deze vullen met een aantal synthetische gegevens. In de volgende query maken we een SampleFruits tabel aan waarin fruitnamen en verkoopprijzen worden opgeslagen. In het volgende deel van het artikel zullen we deze tabel gebruiken.
|
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(‘Appel’,20)
INSERT INTO SampleFruits VALUES(‘Abrikoos’,12)
INSERT INTO SampleFruits VALUES(‘Banaan’,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(‘Mandarijn’,19)
INSERT INTO SampleFruits VALUES(‘Ananas’,22)
GO
SELECT * FROM SampleFruits
|
Wat is Pagineren in SQL Server?
In termen van de SQL Server, het doel van de paginering is, het verdelen van een resultset in discrete pagina’s met behulp van de query. Wanneer de OFFSET- en FETCH-argumenten worden gebruikt in combinatie met de ORDER BY-clausule in een SELECT-instructie, zal dit een paginering-oplossing voor SQL Server zijn.
OFFSET-argument specificeert hoeveel rijen worden overgeslagen uit de resultatenset van de query. In het volgende voorbeeld slaat de query de eerste 3 rijen van de tabel SampleFruits over en geeft vervolgens alle resterende rijen terug.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Wanneer we de OFFSET-waarde op 0 zetten, worden er geen rijen overgeslagen uit de resultatenset. De volgende query kan een voorbeeld zijn van dit gebruikstype:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Als we daarentegen de OFFSET-waarde instellen op een waarde die groter is dan het totale rijenaantal van de resultatenset, zullen er geen rijen worden weergegeven in het resultaat. Wanneer we de volgende query bekijken, het totale aantal rijen van de SampleFruits tabel is 13, en we stellen de OFFSET waarde in op 20, zodat de query geen resultaat zal tonen.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
Het FETCH-argument specificeert hoeveel rijen in het resultaat zullen worden weergegeven, en het FETCH-argument moet samen met het OFFSET-argument worden gebruikt. In het volgende voorbeeld slaan we de eerste 5 rijen over en beperken we de resultatenset tot 6 rijen voor onze voorbeeldtabel.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: De TOP CLAUSE beperkt het aantal rijen dat terugkomt van de SELECT verklaring. Wanneer we de TOP-clausule gebruiken zonder ORDER BY, kunnen willekeurige resultaten worden geretourneerd. Als we het volgende voorbeeld bekijken, zullen er bij elke uitvoering van de query 3 willekeurige rijen worden teruggegeven.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Zoals we hebben geleerd, vereist het OFFSET-FETCH argument de ORDER BY clausule in het SELECT statement. Als we een ongedefinieerde volgorde willen implementeren die lijkt op het eerdere gebruik van de TOP-clausule met OFFSET-FETCH-argumenten, kunnen we een query gebruiken die er uitziet als hieronder:
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Paginatie query in SQL Server
Na het uitzoeken van het antwoord op de “Wat is Paginatie?”, zullen we leren hoe we een paginatie query kunnen schrijven in SQL Server. In het begin zullen we de volgende query uitvoeren en zullen we de query aanpakken:
|
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
|
Zoals we kunnen zien, hebben we twee variabelen gedeclareerd in de bovenstaande query, en deze variabelen zijn:
- @PageNumber – Het specificeert het nummer van de pagina die zal worden weergegeven
- @RowsOfPage – Het specificeert het aantal rijen dat op de pagina zal worden weergegeven. Het resultaat is dat het SELECT statement de tweede pagina weergeeft, die 4 rijen bevat
Dynamisch sorteren met pagineren
Toepassingen moeten soms de gegevens sorteren op basis van verschillende kolommen, hetzij in oplopende of aflopende volgorde naast pagineren. Om aan dit soort eisen te voldoen, kunnen we een ORDER BY-clausule met CASE-condities gebruiken, zodat we een query krijgen die op de variabelen kan worden gesorteerd. De volgende query kan een voorbeeld zijn van dit gebruikstype:
|
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
|
Ook, kunnen we de sorteerkolom en sorteerrichting wijzigen via de variabelen voor de bovenstaande query.
Paginatie in een lus
In dit voorbeeld leren we een query techniek die alle discrete pagina resultaten met een enkele query teruggeeft.
|
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
|
Voor deze query, hebben we een vrij eenvoudige formule gemaakt. Eerst hebben we het totale aantal rijen van de SampleFruit tabel toegewezen aan de @MaxTablePage variabele, en vervolgens hebben we het verdeeld in hoeveel rijen er op een pagina zullen worden weergegeven. We hebben dus het aantal pagina’s berekend dat zal worden weergegeven. De berekende waarde kan echter een decimaal getal zijn, en daarvoor hebben we de CEILING functie gebruikt om het af te ronden op het kleinste gehele getal dat groter is dan het berekende getal. Als tweede stap implementeerden we een WHILE-LOOP en itereerden @PageNumber variabele tot de laatste pagina van het nummer.
Conclusie
In dit artikel hebben we geprobeerd het antwoord te vinden op de vraag “Wat is Pagination?”, met name voor SQL Server. OFFSET-FETCH argumenten helpen om te implementeren hoeveel rijen we willen overslaan en hoeveel rijen we willen weergeven in de resultset wanneer we ze gebruiken met de ORDER BY clausule in de SELECT statements. En tenslotte hebben we geleerd hoe we paginering in SQL Server kunnen bereiken met deze argumenten.
- Auteur
- Recent Posts
Het grootste deel van zijn carrière heeft hij zich gericht op SQL Server Database Administratie en Ontwikkeling. Zijn huidige interesses liggen bij database administratie en Business Intelligence. U kunt hem vinden op LinkedIn.
Bekijk alle berichten van Esat Erkec
- Query optimalisatie mythes – 23 maart, 2021
- Symptomen van Parameter Sniffing in SQL Server – 17 maart 2021
- Het gebruik van Automatic Plan Correction voor Query Tuning – 4 maart 2021