In questo articolo esploriamo le clausole OFFSET e FETCH. OFFSET e FETCH vengono utilizzati insieme alla clausola SELECT statement ORDER BY per fornire un mezzo per recuperare un intervallo di record. La riga iniziale da restituire è determinata dal valore di OFFSET e dal numero massimo di righe da restituire da quel punto in poi da FETCH.
Tutti gli esempi di questa lezione sono basati su Microsoft SQL Server Management Studio e il database AdventureWorks2012. È possibile iniziare a utilizzare questi strumenti gratuiti utilizzando la mia guida Per iniziare a utilizzare SQL Server.
Usare OFFSET e FETCH con la clausola ORDER BY
Restituire le righe da un’istruzione SQL può essere un affare tutto o niente. In molti casi il numero di righe restituite è molto grande e questo può causare problemi se è necessario solo una parte del set di risultati.
Quando i risultati vengono ordinati usando la clausola ORDER BY, alcune opzioni entrano in gioco per limitare il numero di righe restituite:
- Puoi usare TOP per restituire un numero specificato di righe.
- È possibile utilizzare OFFSET e FETCH.
In questo articolo ci immergiamo in saperne di più su OFFSET e FETCH. Per ulteriori informazioni su TOP, leggi l’articolo Guida introduttiva a SQL Server: 2. Ordina i risultati della query.
OFFSET
L’argomento OFFSET viene utilizzato per identificare il punto di partenza per restituire le righe da un risultato. OFFESET è chiamato un argomento poiché è tecnicamente parte della clausola ORDER BY. L’OFFSET è il numero di righe da saltare prima di includerle nel risultato.
La forma generale per l’argomento OFFSET è:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
Dove le righe da saltare sono un valore maggiore o uguale a zero.
Ad esempio, per mostrare tutti tranne i primi 10 dipendenti, ordinati per HireDate potresti scrivere
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
Ecco alcune cose da considerare su OFFSET
- OFFSET fa parte della clausola ORDER BY. Non può essere utilizzato da solo.
- I valori di offset devono essere pari o superiori a zero. Un numero negativo si traduce in un errore.
- Quando l’OFFSET è 0, non vengono saltate righe.
- Se l’OFFSET è maggiore del numero di righe nei risultati ordinati, non vengono restituite righe.
FETCH
L’argomento FETCH viene utilizzato per restituire un determinato numero di righe. FETCH non può essere utilizzato da solo, viene utilizzato in combinazione con OFFSET.
Continuando con il nostro esempio, possiamo mostrare l’11 ° al 15 ° dipendenti assunti utilizzando questa istruzione
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
Nel diagramma seguente puoi vedere OFFSET e FETCH al lavoro. OFFSET viene utilizzato per saltare le prime 10 righe e FETCH viene quindi utilizzato per visualizzare le successive 5.
La combinazione di OFFSET e FETCH rende facile recuperare una finestra “scorrevole” di righe. L’inizio della finestra è determinato da OFFSET e l’altezza da FETCH.
Utilizza per OFFSET e FETCH
Paging
Uno degli usi più popolari per OFFSET e FETCH è il paging. Senza dubbio hai visitato il sito web in cui vedi un elenco di elementi e in fondo c’è un elenco di numeri di pagina o un pulsante Avanti.
Usiamo il paging tutto il tempo sul web. L’esempio più popolare che posso pensare è Google:
I numeri sotto Google rappresentano i numeri di pagina. Una parte dei risultati della ricerca viene restituita con ogni clic.
In modo simile, supponiamo di avere una pagina web che mostra i dipendenti di HireDate. Se volessimo visualizzare 20 dipendenti su una pagina e stavamo visualizzando la 3a pagina (dipendenti 21-30), potremmo utilizzare la seguente query:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Questa query indica a SQL di:
- Selezionare le informazioni sui dipendenti
- Ordinare le informazioni per HireDate
- Saltare 20 righe e iniziare a visualizzare i risultati dal 21 °
- Visualizzare le successive 10 righe di risultati.
Ottenere i record migliori
Se si desidera ottenere le prime dieci righe in una query, è possibile farlo impostando OFFSET su 0. Ricorda che l’OFFSET specifica il numero di righe da saltare. Impostandolo a zero, stiamo dicendo a SQL di iniziare dalla prima riga.
Una volta impostato OFFSET, il prossimo ordine del giorno è quello di impostare FETCH. Dal momento che stiamo cercando la top ten, abbiamo impostato FETCH VICINO a 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Se hai familiarità con la clausola TOP potresti aver notato che ci sono somiglianze tra questi due metodi. Ad esempio, nell’esempio precedente, l’uso di TOP sarebbe simile a
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
Ci sono aspetti in ALTO che non si applicano a OFFSET e FETCH; come essere consentiti nelle istruzioni senza un ORDER BY, ma come puoi vedere per questo esempio, restituiscono risultati equivalenti.
Ottenere i record inferiori
Per ottenere i record inferiori in un set di risultati ci sono due modi. Il primo è ordinare il risultato in ordine decrescente anziché crescente. Questo è pone i risultati in basso verso l’alto. Quindi puoi usare fetch come normale.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Se non si desidera modificare l’ordine dei risultati, è possibile utilizzare una query secondaria per ottenere il conteggio dei record. Questo può quindi essere utilizzato per impostare l’offset di conseguenza.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
C’è un certo pericolo in questo metodo poiché l’espressione per calcolare l’OFFSET può risultare in un valore inferiore a zero. Nel nostro esempio questo potrebbe accadere se il numero totale di righe fosse inferiore a dieci. Per difendersi da questa condizione vorresti incorporare un’istruzione CASE nella tua logica per verificare questa condizione.
Campionamento dei dati nel mezzo di un set di risultati
Una bella caratteristica di FETCH e OFFSET sei in grado di scrivere SQL per campionare o testare i dati dal centro del risultato. Questo è davvero utile se hai bisogno di vedere dare un’occhiata al centro di un set di risultati, forse uno contenente milioni di righe, senza visualizzare tutte le righe fino al punto che desideri rivedere.
Per campionare nel mezzo useresti la stessa logica che faresti per il paging. Naturalmente, il numero di righe che recuperi in questo caso potrebbe essere molto più grande.
Effetto di ORDER BY su OFFSET e FETCH
OFFSET e FETCH funzionano solo in combinazione con una clausola ORDER BY. In effetti, SQL recupera prima i dati specificati, ad esempio le colonne, quindi ordina i dati in ordine crescente o decrescente.
Solo dopo che questo passaggio è stato completato, le righe vengono ignorate e i risultati prodotti.
Casi limite
Poiché alcuni valori possono causare un errore, come un OFFSET negativo, esploriamo varie combinazioni di valori che possono superare il numero di righe in una tabella per capire quali valori sono sicuri da usare e quali valori genererebbero un errore SQL.
Baseremo i nostri esempi su HumanResources.Tabella dei dipendenti, che contiene 290 righe.
L’unico caso che provoca un errore è quando l’OFFSET è negativo. Ci sono casi che non restituiscono righe o possono restituire meno righe di quanto si pensi, ma questi casi non generano errori. Per la maggior parte, queste situazioni si verificano quando il valore di OFFSET è maggiore del numero di righe nel risultato (tutte le righe vengono ignorate), o si sta tentando di recuperare “passato” quindi terminare il risultato.