Klassieke filters in SQL zoals ON, WHERE, en HAVING zijn gebaseerd op predicaten. TOP en OFFSET-FETCH zijn filters die op een ander concept zijn gebaseerd: u geeft de volgorde aan en hoeveel rijen op basis van die volgorde moeten worden gefilterd. Veel filtertaken worden gedefinieerd op basis van volgorde en een vereist aantal rijen. Het is zeker goed om taalondersteuning te hebben in T-SQL die u in staat stelt om het verzoek te formuleren op een manier die overeenkomt met de manier waarop u over de taak denkt.
Dit hoofdstuk begint met de logische ontwerpaspecten van de filters. Vervolgens wordt een paging-scenario gebruikt om de optimalisatie ervan aan te tonen. Het hoofdstuk behandelt ook het gebruik van TOP met modificatie statements. Tenslotte demonstreert het hoofdstuk het gebruik van TOP en OFFSET-FETCH bij het oplossen van praktische problemen zoals top N per groep en mediaan.
De TOP en OFFSET-FETCH filters
U gebruikt de TOP en OFFSET-FETCH filters om op een intuïtieve manier filtereisen in uw query’s te implementeren. De TOP-filter is een propriëtaire functie in T-SQL, terwijl de OFFSET-FETCH-filter een standaardfunctie is. T-SQL begon OFFSET-FETCH te ondersteunen met Microsoft SQL Server 2012. Vanaf SQL Server 2014 mist de implementatie van OFFSET-FETCH in T-SQL nog een paar standaard elementen – interessant genoeg zijn die elementen wel beschikbaar met TOP. Met de huidige implementatie heeft elk van de filters mogelijkheden die niet door de ander worden ondersteund.
Ik zal beginnen met het beschrijven van de logische ontwerpaspecten van TOP en daarna die van OFFSET-FETCH behandelen.
Het TOP-filter
Het TOP-filter is een veelgebruikte constructie in T-SQL. Zijn populariteit kan waarschijnlijk worden toegeschreven aan het feit dat het ontwerp zo goed aansluit bij de manier waarop veel filtervereisten worden uitgedrukt-bijvoorbeeld: “Geef de drie meest recente orders terug.” In dit verzoek is de volgorde voor het filter gebaseerd op orderdate, aflopend, en het aantal rijen dat je wilt filteren op basis van deze volgorde is 3.
Je specificeert de TOP optie in de SELECT lijst met een invoerwaarde getypt als BIGINT die aangeeft hoeveel rijen je wilt filteren. U geeft de orderspecificatie op in de klassieke ORDER BY-clausule. U gebruikt bijvoorbeeld de volgende query om de drie meest recente orders te krijgen.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Ik kreeg de volgende uitvoer van deze query:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
In plaats van het aantal rijen dat u wilt filteren op te geven, kunt u TOP gebruiken om het percentage op te geven (van het totale aantal rijen in het resultaat van de query). U doet dit door een waarde op te geven in het bereik 0 tot 100 (getypt als FLOAT) en het PERCENT sleutelwoord toe te voegen. Bijvoorbeeld, in de volgende query vraagt u om één procent van de rijen te filteren:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
QL Server rondt het aantal rijen berekend op basis van het ingevoerde percentage naar boven af. Bijvoorbeeld, het resultaat van 1 procent toegepast op 830 rijen in de tabel Orders is 8,3. Als je dit getal naar boven afrondt, krijg je 9. Dit is de uitvoer die ik voor deze query kreeg:
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
Merk op dat om het invoerpercentage te vertalen naar een aantal rijen, SQL Server eerst het aantal rijen in het query-resultaat moet berekenen, en dit vereist gewoonlijk extra werk.
Interessant is dat de opdrachtspecificatie optioneel is voor het TOP filter. Neem bijvoorbeeld de volgende query:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Ik kreeg de volgende uitvoer van deze query:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
De selectie van welke drie rijen moeten worden geretourneerd is nondeterministisch. Dit betekent dat als je de query opnieuw uitvoert, zonder dat de onderliggende gegevens veranderen, je theoretisch een andere set van drie rijen zou kunnen krijgen. In de praktijk zal de rijen selectie afhangen van fysieke condities zoals optimalisatie keuzes, opslag engine keuzes, data layout, en andere factoren. Als je de query meerdere keren uitvoert, zolang die fysieke condities niet veranderen, is er een kans dat je dezelfde resultaten blijft krijgen. Maar het is van cruciaal belang om het principe van “fysieke gegevensonafhankelijkheid” van het relationele model te begrijpen, en te onthouden dat je op het logische niveau geen garantie hebt voor herhaalbare resultaten. Zonder volgorde specificatie, moet je de volgorde als willekeurig beschouwen, resulterend in een nondeterministische rij selectie.
Zelfs als je wel volgorde specificatie geeft, betekent het niet dat de query deterministisch is. Bijvoorbeeld, een eerdere TOP query gebruikte orderdate, DESC als de specificatie van de volgorde. De kolom orderdatum is niet uniek; daarom is de selectie tussen rijen met dezelfde orderdatum nondeterministisch. Dus wat doe je in gevallen waar je determinisme moet garanderen? Er zijn twee mogelijkheden: WITH TIES gebruiken of unieke volgorde.
De WITH TIES optie zorgt ervoor dat ties in het resultaat worden opgenomen. Hier is hoe je het toepast op ons voorbeeld:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Hier is het resultaat dat ik kreeg van deze query:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
SQL Server filtert de drie rijen met de meest recente besteldata, plus het neemt alle andere rijen op die dezelfde besteldatum hebben als in de laatste rij. Als resultaat kun je meer rijen krijgen dan het aantal dat je hebt opgegeven. In deze query, specificeerde je dat je drie rijen wilde filteren, maar je kreeg er uiteindelijk vier. Wat hier interessant is om op te merken is dat de rij selectie nu deterministisch is, maar de presentatie volgorde tussen rijen met dezelfde order datum is nondeterministisch.
De tweede methode om een deterministisch resultaat te garanderen is om de volgorde specificatie uniek te maken door een tiebreaker toe te voegen. In ons voorbeeld zou u bijvoorbeeld orderid, DESC als tiebreaker kunnen toevoegen. Dit betekent dat, in het geval van gelijke order datum waarden, een rij met een hogere order ID waarde de voorkeur krijgt boven een rij met een lagere. Hier is onze query met de tiebreaker toegepast:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Deze query genereert de volgende output:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Het gebruik van de unieke volgorde maakt zowel de rij selectie als de presentatie volgorde deterministisch. Zowel de resultatenverzameling als de presentatievolgorde van de rijen zijn gegarandeerd herhaalbaar zolang de onderliggende gegevens niet veranderen.
Als u een geval hebt waarin u een bepaald aantal rijen moet filteren maar echt niet om de volgorde geeft, kan het een goed idee zijn om ORDER BY (SELECT NULL) te specificeren, zoals dit:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
Op deze manier laat u iedereen weten dat uw keuze voor een willekeurige volgorde opzettelijk is, wat verwarring en twijfel helpt voorkomen.
Als herinnering aan wat ik heb uitgelegd in Hoofdstuk 1, “Logische query verwerking,” over de TOP en OFFSET-FETCH filters, presentatie volgorde is alleen gegarandeerd als de buitenste query een ORDER BY clausule heeft. Bijvoorbeeld, in de volgende query presentatie is de volgorde niet gegarandeerd:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Om een presentatie-volgorde garantie te geven, moet u een ORDER BY clausule specificeren in de buitenste query, als volgt:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS DORDER BY orderdate DESC, orderid DESC;
De OFFSET-FETCH filter
De OFFSET-FETCH filter is een standaard functie die is ontworpen vergelijkbaar met TOP, maar met een extra element. U kunt opgeven hoeveel rijen u wilt overslaan voordat u opgeeft hoeveel rijen u wilt filteren.
Zoals u al kon raden, kan deze functie handig zijn bij het implementeren van paging-oplossingen – dat wil zeggen, het resultaat op verzoek in één keer aan de gebruiker terugzenden wanneer de volledige resultatenverzameling te lang is om in één scherm of webpagina te passen.
De OFFSET-FETCH filter vereist dat er een ORDER BY-clausule bestaat, en deze wordt direct daarna gespecificeerd. U geeft eerst aan hoeveel rijen moeten worden overgeslagen in een OFFSET-clausule, gevolgd door hoeveel rijen moeten worden gefilterd in een FETCH-clausule. Bijvoorbeeld, gebaseerd op de aangegeven volgorde, slaat de volgende query de eerste 50 rijen over en filtert de volgende 25 rijen:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Met andere woorden, de query filtert de rijen 51 tot en met 75. In pagineringstermen, uitgaande van een paginagrootte van 25 rijen, geeft deze query de derde pagina terug.
Om natuurlijke declaratieve taal mogelijk te maken, kunt u het sleutelwoord FIRST gebruiken in plaats van NEXT als u dat wilt, hoewel de betekenis hetzelfde is. Het gebruik van FIRST kan intuïtiever zijn als je geen rijen overslaat. Zelfs als u geen rijen wilt overslaan, maakt T-SQL het nog steeds verplicht om de OFFSET clausule (met 0 ROWS) te specificeren om parsing ambiguïteit te vermijden. Op dezelfde manier, in plaats van de meervoudsvorm van het keyword ROWS te gebruiken, kunt u de enkelvoudsvorm ROW gebruiken in zowel de OFFSET als de FETCH clausules. Dit is natuurlijker als je slechts één rij moet overslaan of filteren.
Als je nieuwsgierig bent wat het doel is van het sleutelwoord ONLY, het betekent dat er geen banden moeten worden opgenomen. Standaard SQL definieert het alternatief WITH TIES; T-SQL ondersteunt dit echter nog niet. Op dezelfde manier definieert standaard SQL de PERCENT optie, maar T-SQL ondersteunt deze ook nog niet. Deze twee ontbrekende opties zijn beschikbaar met het TOP filter.
Zoals gezegd, het OFFSET-FETCH filter vereist een ORDER BY clausule. Als u een willekeurige volgorde wilt gebruiken, zoals TOP zonder een ORDER BY clausule, kunt u de truc met ORDER BY (SELECT NULL) gebruiken, zoals zo:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
De FETCH clausule is optioneel. Als u een bepaald aantal rijen wilt overslaan, maar geen limiet wilt stellen aan het aantal terug te geven rijen, hoeft u alleen maar geen FETCH clausule op te geven. De volgende query slaat bijvoorbeeld 50 rijen over, maar beperkt het aantal terug te geven rijen niet:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Met betrekking tot presentatievolgorde is het gedrag hetzelfde als met de TOP filter; namelijk, met OFFSET-FETCH ook, presentatievolgorde is alleen gegarandeerd als de buitenste query een ORDER BY clausule heeft.