Paginierung ist ein Prozess, der verwendet wird, um große Daten in kleinere diskrete Seiten aufzuteilen, und dieser Prozess ist auch als Paging bekannt. Die Paginierung wird häufig von Webanwendungen verwendet und kann bei Google beobachtet werden. Wenn wir bei Google nach etwas suchen, werden die Ergebnisse auf einer separaten Seite angezeigt; dies ist die Hauptidee der Paginierung.
In den nächsten Teilen des Artikels werden wir nun erörtern, wie die Paginierung in SQL Server erreicht werden kann.
Vorbereiten von Beispieldaten
Bevor wir uns im Detail mit der Paginierung befassen, werden wir eine Beispieltabelle erstellen und diese mit einigen synthetischen Daten befüllen. In der folgenden Abfrage erstellen wir eine Tabelle SampleFruits, die Obstnamen und Verkaufspreise speichert. Im nächsten Teil des Artikels werden wir diese Tabelle verwenden.
|
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 BeispielFrüchte VALUES(‚Aprikose‘,12)
INSERT INTO BeispielFrüchte VALUES(‚Banane‘,8)
INSERT INTO MusterFrüchte VALUES(‚Kirsche‘,11)
INSERT INTO MusterFrüchte VALUES(‚Erdbeere‘,26)
INSERT INTO MusterFrüchte VALUES(‚Zitrone‘,4)
INSERT INTO MusterFrüchte VALUES(‚Kiwi‘,14)
INSERT INTO BeispielFrüchte VALUES(‚Kokosnuss‘,34)
INSERT INTO BeispielFrüchte VALUES(‚Orange‘,24)
INSERT INTO BeispielFrüchte VALUES(‚Himbeere‘,13)
INSERT INTO BeispielFrüchte VALUES(‚Mango‘,9)
INSERT INTO BeispielFrüchte VALUES(‚Mandarine‘,19)
INSERT INTO BeispielFrüchte VALUES(‚Ananas‘,22)
GO
SELECT * FROM SampleFruits
|
Was ist Pagination in SQL Server?
In Bezug auf den SQL Server ist das Ziel der Paginierung, eine Ergebnismenge mit Hilfe der Abfrage in einzelne Seiten zu unterteilen. Wenn die OFFSET- und FETCH-Argumente zusammen mit der ORDER BY-Klausel in einer SELECT-Anweisung verwendet werden, handelt es sich um eine Paginierungslösung für SQL Server.
Das OFFSET-Argument gibt an, wie viele Zeilen in der Ergebnismenge der Abfrage übersprungen werden sollen. Im folgenden Beispiel überspringt die Abfrage die ersten 3 Zeilen der Tabelle SampleFruits und gibt dann alle übrigen Zeilen zurück.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Wenn wir den OFFSET-Wert auf 0 setzen, werden keine Zeilen aus der Ergebnismenge ausgelassen. Die folgende Abfrage ist ein Beispiel für diese Art der Verwendung:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Wenn wir hingegen einen OFFSET-Wert festlegen, der größer ist als die Gesamtzahl der Zeilen des Resultsets, werden keine Zeilen im Ergebnis angezeigt. Bei der folgenden Abfrage beträgt die Gesamtzahl der Zeilen in der Tabelle SampleFruits 13, und wir haben den OFFSET-Wert auf 20 gesetzt, sodass die Abfrage kein Ergebnis anzeigt.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
Das FETCH-Argument gibt an, wie viele Zeilen im Ergebnis angezeigt werden sollen, und das FETCH-Argument muss mit dem OFFSET-Argument verwendet werden. Im folgenden Beispiel werden die ersten 5 Zeilen übersprungen und die Ergebnismenge auf 6 Zeilen für unsere Beispieltabelle begrenzt.
|
1
2
3
4
|
SELECT FruitName, Preis
FROM MusterFrüchte
ORDER BY Preis
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tipp: Die TOP CLAUSE begrenzt die Anzahl der Zeilen, die von der SELECT-Anweisung zurückgegeben werden. Wenn wir die TOP-Klausel ohne ORDER BY verwenden, können beliebige Ergebnisse zurückgegeben werden. Im folgenden Beispiel werden bei jeder Ausführung der Abfrage 3 zufällige Zeilen zurückgegeben.
|
1
2
|
SELECT TOP 7 FruitName, Preis
FROM SampleFruits
|
Wie wir gelernt haben, erfordert das Argument OFFSET-FETCH die ORDER BY-Klausel in der SELECT-Anweisung. Wenn wir eine undefinierte Reihenfolge implementieren wollen, die der früheren Verwendung der TOP-Klausel mit OFFSET-FETCH-Argumenten entspricht, können wir eine Abfrage verwenden, die wie folgt aussieht:
|
1
2
3
|
SELECT FruitName ,Preis FROM MusterFrüchte
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Paginierungsabfrage in SQL Server
Nachdem wir die Antwort auf die Frage „Was ist Pagination?“ herausgefunden haben, werden wir lernen, wie wir eine Paginierungsabfrage in SQL Server schreiben können. Als erstes werden wir die folgende Abfrage ausführen und die Abfrage in Angriff nehmen:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Preis FROM MusterFrüchte
ORDER BY Preis
OFFSET (@Seitennummer-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Wie wir sehen können, haben wir zwei Variablen in der obigen Abfrage deklariert, und diese Variablen sind:
- @PageNumber – Sie gibt die Nummer der Seite an, die angezeigt werden soll
- @RowsOfPage – Sie gibt an, wie viele Zeilen auf der Seite angezeigt werden sollen. Als Ergebnis zeigt die SELECT-Anweisung die zweite Seite an, die 4 Zeilen enthält
Dynamische Sortierung mit Paginierung
Anwendungen müssen die Daten möglicherweise nach verschiedenen Spalten entweder in aufsteigender oder absteigender Reihenfolge neben der Paginierung sortieren. Um diese Art von Anforderung zu erfüllen, können wir eine ORDER BY-Klausel mit CASE-Bedingungen verwenden, so dass wir eine Abfrage erhalten, die nach den Variablen sortiert werden kann. Die folgende Abfrage kann ein Beispiel für diese Art der Verwendung sein:
|
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,Preis FROM MusterFrüchte
ORDER BY
CASE WHEN @SortingCol = ‚Preis‘ AND @SortType =’ASC‘ THEN Preis END ,
CASE WHEN @SortingCol = ‚Preis‘ AND @SortType =’DESC‘ THEN Preis END DESC,
CASE WHEN @SortingCol = ‚Fruchtname‘ AND @SortType =’ASC‘ THEN Fruchtname END ,
CASE WHEN @SortingCol = ‚FruitName‘ AND @SortType =’DESC‘ THEN FruitName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Außerdem, können wir die Sortierspalte und die Sortierrichtung über die Variablen für die obige Abfrage ändern.
Paginierung in einer Schleife
In diesem Beispiel lernen wir eine Abfragetechnik kennen, die mit einer einzigen Abfrage alle diskreten Seitenergebnisse liefert.
|
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,Preis FROM MusterFrüchte
ORDER BY Preis
OFFSET (@Seitennummer-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Für diese Abfrage, haben wir eine recht einfache Formel erstellt. Zunächst haben wir die Gesamtzahl der Zeilen der Tabelle SampleFruit der Variablen @MaxTablePage zugewiesen und dann durch die Anzahl der Zeilen, die auf einer Seite angezeigt werden, geteilt. So haben wir die Anzahl der Seiten berechnet, die angezeigt werden. Der berechnete Wert kann jedoch eine Dezimalzahl sein, und deshalb haben wir die Funktion CEILING verwendet, um ihn auf die kleinste Ganzzahl aufzurunden, die größer ist als die berechnete Zahl. In einem zweiten Schritt haben wir ein WHILE-LOOP implementiert und die Variable @PageNumber bis zur letzten Seite der Nummer iteriert.
Abschluss
In diesem Artikel haben wir versucht, die Antwort auf die Frage „Was ist Pagination?“ herauszufinden, insbesondere für SQL Server. Die OFFSET-FETCH-Argumente helfen bei der Implementierung der Anzahl der Zeilen, die übersprungen werden sollen, und der Anzahl der Zeilen, die in der Ergebnismenge angezeigt werden sollen, wenn wir sie mit der ORDER BY-Klausel in den SELECT-Anweisungen verwenden. Und schließlich haben wir gelernt, wie wir mit diesen Argumenten eine Paginierung in SQL Server erreichen können.
- Autor
- Recent Posts
Die meiste Zeit seiner beruflichen Laufbahn konzentrierte er sich auf die Verwaltung und Entwicklung von SQL Server-Datenbanken. Sein derzeitiges Interesse gilt der Datenbankverwaltung und Business Intelligence. Sie können ihn auf LinkedIn finden.
Alle Beiträge von Esat Erkec anzeigen
- Query Optimization Myths – March 23, 2021
- Symptome von Parameter-Sniffing in SQL Server – 17. März 2021
- Automatische Plankorrektur für Abfrageoptimierung verwenden – 4. März 2021