È possibile utilizzare sia TOP che OFFSET & FETCH limitare il numero di righe restituite. OFFSET e FETCH possono restituire risultati simili allinizio, ma ci sono differenze che possono influenzare il metodo migliore da utilizzare nella situazione specifica.
Tutti gli esempi di questa lezione si basano sulla gestione di Microsoft SQL Server Studio e il database AdventureWorks2012. Puoi iniziare a utilizzare questi strumenti gratuiti utilizzando la mia Guida introduttiva allutilizzo di SQL Server.
Qual è la differenza tra TOP e OFFSET & Fetch?
Nelle sezioni seguenti trattiamo TOP e OFFSET & FETCH. Penso che una volta lette entrambe le sezioni capirai le loro differenze. Questi sono anche riassunti in una tabella alla fine di questo articolo.
Top
TOP viene utilizzato per restituire un numero specifico di righe dal risultato di una query.
La clausola TOP è disponibile da tempo nelle versioni precedenti di SQL server, come SQL 2005. TOP non è conforme ad ANSI e il suo utilizzo è limitato ai prodotti Microsoft come SQL Server e MS-Access.
La clausola TOP viene utilizzata nellelenco delle colonne dellistruzione SELECT e può essere utilizzata con o senza ORDER BY.
Ad esempio, per selezionare i dieci prodotti meno costosi, utilizzare
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
Sebbene ORDER BY non sia richiesto, è una best practice, poiché senza di esso non hai alcuna garanzia per quali righe vengono restituite.
Un elemento unico per TOP è la capacità di restituire una percentuale di righe. Listruzione
SELECT TOP 10 PERCENT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
restituisce i primi 51 prodotti con il costo standard più basso (ad es. 504 righe x 10% = 50,4, arrotondato a 51).
È anche possibile selezionare il numero specificato di record dalla parte inferiore del risultato di una query. Per fare ciò puoi usare un trucco per ordinare i record in ordine decrescente in questo modo:
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC
Tieni presente che quando i risultati sono ordinati in ordine decrescente, il gli importi maggiori vengono restituiti per primi. Per questo motivo, TOP restituisce quei prodotti con il costo standard più elevato.
TOP può essere molto utile per risultati di grandi dimensioni poiché la restituzione di migliaia di righe può influire sulle prestazioni. In molti casi, gli utilizzi sfogliano solo la parte iniziale dei risultati.
TOP can display ties vale – WITH TIES ti consente di visualizzare anche record aggiuntivi che hanno lo stesso valore dellultima riga dal risultato di base.
OFFSET e FETCH
OFFSET e FETCH vengono utilizzati per restituire una finestra di record da un set di risultati. OFFSET specifica quante righe saltare allinterno del risultato e FETCH specifica quante righe da quel punto in avanti restituire nel risultato.
OFFSET e FETCH sono stati recentemente introdotti in SQL Server 2012 e sono conformi ad ANSI.
Puoi usare OFFSET senza FETCH, ma FETCH non può essere usato da solo. Indipendentemente da ciò, OFFSET deve essere utilizzato con una clausola ORDER BY. Il motivo è semplice in quanto OFFSET e FETCH fanno parte della clausola ORDER BY.
In questo esempio le prime dieci righe del risultato vengono saltate, quindi le 10 successive vengono visualizzate nel risultato.
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
È possibile replicare le righe restituite da TOP utilizzando OFFSET e FETCH a condizione che i risultati siano ordinati. La seguente istruzione equivale a restituire la TOP 10:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Quando OFFSET è impostato su 0, nessuna riga viene ignorata.
A differenza di TOP, con OFFSET non ci sono mezzi incorporati per restituire una percentuale di righe. Puoi comunque tirare il tuo calcolando la percentuale in questo modo:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT (SELECT CAST(CEILING(COUNT(*) * .1) as INT) FROM Production.Product) ROWS ONLY
Il trucco sta nella sottoquery. Calcolando il numero di righe nel risultato siamo in grado di ottenere una percentuale. Ecco la sottoquery. Se lo desideri, puoi eseguirlo in SQL Server Management Studio.
SELECT CAST(CEILING(COUNT(*) * .1) as INT)FROM Production.Product
Sono presenti diversi elementi da notare:
- CEILING arrotonda i numeri per eccesso. Nel nostro esempio CEILING (50.4) restituisce 51.0. Questo imita il comportamento di TOP.
- FETCH richiede un valore intero, quindi CAST viene utilizzato per effettuare tale conversione. Così 51.0 diventa, 51
Per restituire gli ultimi 10 record di un risultato ci sono due metodi. Possiamo invertire lordinamento come abbiamo fatto per TOP:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Oppure possiamo mantenere lordinamento e calcolare loffset in modo che tutte le righe fino allultima dieci vengono saltati. Esploriamo questa tecnica in modo più dettagliato nella lezione SQL su OFFSET e FETCH.
Riepilogo delle differenze
Ecco un riepilogo di alcune delle principali differenze tra TOP e OFFSET & FETCH.
In generale, se il tuo obiettivo è restituire solo le prime diverse righe, penso che TOP sia comunque una soluzione semplice; se stai cercando una soluzione di paging o hai bisogno di selezionare le righe al centro o alla fine dei risultati della query senza influenzare lordinamento originale, allora penso che OFFSET & FETCH sono i migliori.