La paginazione è un processo che viene utilizzato per dividere un grande dato in pagine discrete più piccole, e questo processo è anche conosciuto come paginazione. La paginazione è comunemente usata dalle applicazioni web e può essere vista su Google. Quando cerchiamo qualcosa su Google, mostra i risultati sulla pagina separata; questa è l’idea principale della paginazione.
Ora, discuteremo come realizzare la paginazione in SQL Server nelle prossime parti dell’articolo.
Preparazione dei dati di esempio
Prima di iniziare ad entrare nel dettaglio della paginazione, creeremo una tabella di esempio e la popoleremo con alcuni dati sintetici. Nella seguente query, creeremo una tabella SampleFruits che memorizza i nomi dei frutti e i prezzi di vendita. Nella prossima parte dell’articolo, useremo questa tabella.
|
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(‘Albicocca’,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(‘Cocco’,34)
INSERT INTO SampleFruits VALUES(‘Arancia’,24)
INSERT INTO SampleFruits VALUES(‘Lampone’,13)
INSERT INTO SampleFruits VALUES(‘Mango’,9)
INSERT INTO SampleFruits VALUES(‘Mandarino’,19)
INSERT INTO SampleFruits VALUES(‘Ananas’,22)
GO
SELECT * FROM SampleFruits
|
Cos’è la paginazione in SQL Server?
In termini di SQL Server, lo scopo della paginazione è, dividere un insieme di risultati in pagine discrete con l’aiuto della query. Quando gli argomenti OFFSET e FETCH sono usati con la clausola ORDER BY in una dichiarazione SELECT, sarà una soluzione di paginazione per SQL Server.
L’argomento OFFSET specifica quante righe saranno saltate dal set di risultati della query. Nell’esempio seguente, la query salterà le prime 3 righe della tabella SampleFruits e poi restituirà tutte le altre righe.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Quando impostiamo il valore OFFSET a 0, nessuna riga verrà saltata dal set di risultati. La seguente query può essere un esempio di questo tipo di utilizzo:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
D’altra parte, se impostiamo il valore OFFSET, che è maggiore del numero totale di righe del resultset, nessuna riga sarà visualizzata sul risultato. Quando consideriamo la seguente query, il numero totale di righe della tabella SampleFruits è 13, e abbiamo impostato il valore OFFSET a 20, quindi la query non visualizzerà alcun risultato.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
L’argomento FETCH specifica quante righe saranno visualizzate nel risultato e l’argomento FETCH deve essere usato con l’argomento OFFSET. Nell’esempio seguente, salteremo le prime 5 righe e poi limiteremo il set di risultati a 6 righe per la nostra tabella di esempio.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: La clausola TOP limita il numero di righe restituite dall’istruzione SELECT. Quando usiamo la clausola TOP senza ORDER BY, possono essere restituiti risultati arbitrari. Se consideriamo il seguente esempio, restituirà 3 righe casuali ad ogni esecuzione della query.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Come abbiamo imparato, l’argomento OFFSET-FETCH richiede la clausola ORDER BY nella dichiarazione SELECT. Se vogliamo implementare un ordine indefinito, come l’uso precedente della clausola TOP con argomenti OFFSET-FETCH, possiamo usare una query che assomiglia alla seguente:
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Query di paginazione in SQL Server
Dopo aver capito la risposta alla domanda “Cos’è la paginazione?”impareremo come scrivere una query di paginazione in SQL Server. All’inizio, eseguiremo la seguente query e la affronteremo:
|
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
|
Come possiamo vedere, abbiamo dichiarato due variabili nella query di cui sopra, e queste variabili sono:
- @PageNumber – Specifica il numero della pagina che sarà visualizzata
- @RowsOfPage – Specifica quanti numeri di righe saranno visualizzati nella pagina. Come risultato, l’istruzione SELECT visualizza la seconda pagina, che contiene 4 righe
Ordinamento dinamico con paginazione
Le applicazioni possono avere bisogno di ordinare i dati secondo diverse colonne sia in ordine ascendente che discendente accanto alla paginazione. Per superare questo tipo di esigenza, possiamo usare una clausola ORDER BY con condizioni CASE in modo da ottenere una query che può essere ordinata in base alle variabili. La seguente query può essere un esempio di questo tipo di utilizzo:
|
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
|
Inoltre, possiamo cambiare la colonna di ordinamento e la direzione di ordinamento attraverso le variabili per la query di cui sopra.
Paginazione in un Loop
In questo esempio, impareremo una tecnica di query che restituisce tutti i risultati delle pagine discrete con una sola query.
|
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
|
Per questa query, abbiamo creato una formula abbastanza semplice. All’inizio, abbiamo assegnato il numero totale di righe della tabella SampleFruit alla variabile @MaxTablePage, e poi lo abbiamo diviso in quante righe saranno visualizzate in una pagina. Quindi, abbiamo calcolato il numero di pagine che verranno visualizzate. Tuttavia, il valore calcolato può essere un decimale, e per questo, abbiamo usato la funzione CEILING per arrotondarlo al più piccolo numero intero che è più grande del numero calcolato. Come secondo passo, abbiamo implementato un WHILE-LOOP e iterato la variabile @PageNumber fino all’ultima pagina del numero.
Conclusione
In questo articolo, abbiamo cercato di trovare la risposta alla domanda “Cos’è la paginazione? Gli argomenti OFFSET-FETCH aiutano ad implementare quante righe vogliamo saltare e quante righe vogliamo visualizzare nel set di risultati quando li usiamo con la clausola ORDER BY nelle istruzioni SELECT. E infine, abbiamo imparato come possiamo ottenere la paginazione in SQL Server con questi argomenti.
- Autore
- Postati recenti
La maggior parte della sua carriera si è concentrata sull’amministrazione e lo sviluppo di database SQL Server. I suoi interessi attuali sono nell’amministrazione di database e nella Business Intelligence. Potete trovarlo su LinkedIn.
Vedi tutti i post di Esat Erkec
- Miti di ottimizzazione delle query – March 23, 2021
- Sintomi del Parameter Sniffing in SQL Server – 17 marzo 2021
- Utilizzo della correzione automatica del piano per l’ottimizzazione delle query – 4 marzo 2021