Zowel TOP als OFFSET & FETCH kan worden gebruikt om beperk het aantal geretourneerde rijen. OFFSET en FETCH kunnen vergelijkbare resultaten bovenaan weergeven, maar er zijn verschillen die van invloed kunnen zijn op welke methode u het beste kunt gebruiken in uw gegeven situatie.
Alle voorbeelden voor deze les zijn gebaseerd op Microsoft SQL Server Management Studio en de AdventureWorks2012-database. U kunt aan de slag met deze gratis tools met behulp van mijn Gids Aan de slag met SQL Server.
Wat is het verschil tussen TOP en OFFSET & Fetch?
In de volgende secties behandelen we TOP en OFFSET & FETCH. Ik denk dat als je beide secties hebt gelezen, je de verschillen zult begrijpen. Deze worden ook samengevat in een tabel aan het einde van dit artikel.
Top
TOP wordt gebruikt om een specifiek aantal rijen uit een queryresultaat te retourneren.
De TOP-clausule is al enige tijd beschikbaar in oudere versies van SQL Server, zoals SQL 2005. TOP is niet ANSI-compatibel en het gebruik ervan is meestal beperkt tot Microsoft-producten zoals SQL Server en MS-Access.
De TOP-clausule wordt gebruikt in de kolomlijst van de SELECT-instructie en kan met of zonder ORDER BY worden gebruikt.
Gebruik bijvoorbeeld om de tien minst dure producten te selecteren
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
Hoewel de ORDER BY niet vereist is, is dit de beste praktijk, want zonder deze code hebt u geen garantie voor welke rijen worden geretourneerd.
Eén item dat uniek is voor TOP is de mogelijkheid om een percentage rijen te retourneren. De instructie
SELECT TOP 10 PERCENT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
retourneert de top 51 producten met de laagste StandardCost (dwz 504 rijen x 10% = 50,4, afgerond naar boven).
U kunt ook een bepaald aantal records selecteren onder aan een queryresultaat. Om dit te doen kunt u een truc gebruiken om de records in aflopende volgorde te ordenen:
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC
Houd er rekening mee dat wanneer de resultaten in aflopende volgorde worden gesorteerd, de grootste bedragen worden eerst geretourneerd. Daarom retourneert TOP die producten met de hoogste standaardkosten.
TOP kan erg handig zijn bij grote resultaten, omdat het retourneren van duizenden rijen de prestaties kan beïnvloeden. In veel gevallen bladert het gebruik gewoon door het begin van de resultaten.
TOP kan ties vale weergeven – WITH TIES stelt u in staat om ook extra records weer te geven die dezelfde waarde hebben als de laatste rij van het basisresultaat.
OFFSET en FETCH
OFFSET en FETCH worden gebruikt om een venster met records uit een resultaatset te retourneren. OFFSET geeft aan hoeveel rijen er binnen het resultaat moeten worden overgeslagen, en FETCH geeft aan hoeveel rijen vanaf dat punt naar voren moeten worden geretourneerd in het resultaat.
OFFSET en FETCH zijn onlangs geïntroduceerd in SQL Server 2012 en zijn ANSI-compatibel.
U kunt OFFSET gebruiken zonder FETCH, maar FETCH kan niet op zichzelf worden gebruikt. OFFSET moet hoe dan ook worden gebruikt met een ORDER BY-clausule. De reden is simpel, aangezien OFFSET en FETCH deel uitmaken van de ORDER BY-clausule.
In dit voorbeeld worden de eerste tien rijen van het resultaat overgeslagen, waarna de volgende 10 in het resultaat worden weergegeven.
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
U kunt de rijen geretourneerd van TOP repliceren met OFFSET en FETCH, op voorwaarde dat de resultaten zijn geordend. De volgende instructie is hetzelfde als het retourneren van de TOP 10:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Als OFFSET is ingesteld op 0, worden er geen rijen overgeslagen.
In tegenstelling tot TOP, met OFFSET zijn er geen ingebouwde middelen om een percentage rijen te retourneren. U kunt echter zelf een rol spelen door het percentage als volgt te berekenen:
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
De truc zit in de subquery. Door het aantal rijen in het resultaat te berekenen, kunnen we vervolgens een percentage verkrijgen. Hier is de subquery. Als u wilt, kunt u dit uitvoeren in SQL Server Management Studio.
SELECT CAST(CEILING(COUNT(*) * .1) as INT)FROM Production.Product
Er zijn verschillende items Let op:
- CEILING rondt getallen naar boven af. In ons voorbeeld geeft CEILING (50.4) 51.0 terug. Dit bootst het gedrag van TOP na.
- FETCH vereist een geheel getal, dus CAST wordt gebruikt om die conversie uit te voeren. Dus 51.0 wordt 51
Om de onderste 10 records van een resultaat te retourneren, zijn er twee methoden. We kunnen de sorteervolgorde omkeren zoals we deden voor TOP:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Of we kunnen de sorteervolgorde behouden en de offset zo berekenen dat alle rijen tot de laatste tien worden overgeslagen. We onderzoeken deze techniek in meer detail in de SQL-les over OFFSET en FETCH.
Samenvatting van verschillen
Hier is een samenvatting van enkele van de belangrijkste verschillen tussen TOP en OFFSET & FETCH.
In het algemeen, als het je doel is om alleen de eerste paar rijen terug te geven, dan denk ik echter dat TOP een eenvoudige oplossing is; als u op zoek bent naar een paging-oplossing, of als u rijen in het midden of onderaan de queryresultaten moet selecteren zonder de oorspronkelijke sorteervolgorde te beïnvloeden, dan denk ik dat OFFSET & FETCH zijn het beste.