L’impaginazione viene spesso utilizzata nelle applicazioni in cui l’utente può fare clic su Precedente / Successivo per navigare nelle pagine che compongono i risultati o fare clic su un numero di pagina per accedere direttamente a una pagina specifica.
Quando si eseguono query in SQL Server, è possibile impaginare i risultati utilizzando gli argomentiOFFSETeFETCHdella clausolaORDER BY. Questi argomenti sono stati introdotti in SQL Server 2012, quindi è possibile utilizzare questa tecnica se si dispone di SQL Server 2012 o superiore.
In questo contesto, l’impaginazione è dove si dividono i risultati della query in blocchi più piccoli, ogni blocco continua dove è finito il precedente. Ad esempio, se una query restituisce 1000 righe, è possibile impaginarle in modo che vengano restituite in gruppi di 100. Un’applicazione può passare il numero di pagina e la dimensione della pagina a SQL Server, e SQL Server può quindi utilizzarlo per restituire solo i dati per la pagina richiesta.
Esempio 1 – Nessuna impaginazione
Per prima cosa, eseguiamo una query che restituisce tutte le righe in una tabella:
SELECT *FROM GenresORDER BY GenreId;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
Questo esempio non utilizza l’impaginazione: vengono visualizzati tutti i risultati.
Questo set di risultati è così piccolo che normalmente non richiederebbe l’impaginazione, ma ai fini di questo articolo, impaginiamolo.
Esempio 2 – Visualizzare i Primi 3 Risultati
in Questo esempio mostra i primi tre risultati:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
In questo caso, è possibile specificare che i risultati dovrebbero iniziare il primo risultato e visualizzare i prossimi tre righe. Questo viene fatto usando quanto segue:
-
OFFSET 0 ROWSspecifica che non deve esserci alcun offset (un offset pari a zero). -
FETCH NEXT 3 ROWS ONLYottiene le tre righe successive dall’offset. Poiché ho specificato un offset di zero, le prime tre righe vengono recuperate.
Se tutto quello che volevamo erano i primi 3 risultati, avremmo potuto ottenere lo stesso risultato usando la clausola TOP invece di specificare i valori di offset e fetch. Tuttavia, questo non ci avrebbe permesso di fare la parte successiva.
Esempio 3 – Visualizza i prossimi 3 risultati
Ora visualizziamo i prossimi tre risultati:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Quindi l’unica cosa che ho cambiato è stato l’offset.
I valori di offset e recupero possono anche essere un’espressione fornita come variabile, parametro o sottoquery scalare costante. Quando viene utilizzata una subquery, non può fare riferimento a nessuna colonna definita nell’ambito della query esterna (non può essere correlata con la query esterna).
I seguenti esempi utilizzano espressioni per mostrare due approcci per impaginare i risultati.
Esempio 4 – Impaginazione per numero di riga
Questo esempio utilizza le espressioni per specificare il numero di riga da cui iniziare.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Qui, uso @StartRow int = 1 per specificare che i risultati dovrebbero iniziare dalla prima riga.
Ecco cosa succede se incremento quel valore su2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
Inizia dalla seconda riga. Usando questo metodo, posso specificare la riga esatta da cui iniziare.
Esempio 5 – Impaginazione per numero di pagina
Questo esempio è quasi identico all’esempio precedente, tranne per il fatto che consente di specificare il numero di pagina, anziché il numero di riga.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Quindi il primo risultato è lo stesso. Tuttavia, vediamo cosa succede quando incrementiamo@PageNumber a2 (ho rinominato questa variabile per riflettere il suo nuovo scopo).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Questa volta i risultati iniziano dalla quarta riga. Quindi usando questo metodo puoi semplicemente passare il numero di pagina piuttosto che il numero di riga.
Esempio 6 – Impaginazione Loop
Per finire, ecco un rapido esempio che scorre in tutte le pagine e specifica l’iniziale numero di riga per ogni iterazione:
DECLARE @StartRow int = 1, @RowsPerPage int = 3;WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow BEGIN SELECT * FROM Genres ORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;SET @StartRow = @StartRow + @RowsPerPage; CONTINUEEND;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 7 | Rap || 8 | Punk |+-----------+---------+(2 rows affected)
Esempio 7 – RIGA vs RIGHE
Se si verifica il codice che utilizza ROW invece di ROWS, sia argomenti di fare la stessa cosa. Sono sinonimi e sono forniti per la compatibilità ANSI.
Ecco il primo esempio in questa pagina, ma con ROW invece di ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Esempio 8 – PRIMA vs AVANTI
Lo stesso vale per FIRST e NEXT. Questi sono sinonimi forniti per la compatibilità ANSI.
Ecco l’esempio precedente ma conFIRSTinvece diNEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
Risultato:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+