Klassiska filter i SQL som ON, WHERE och HAVING är baserade på predikat. TOP och OFFSET-FETCH är filter som bygger på ett annat koncept: du anger ordning och hur många rader som ska filtreras utifrån den ordningen. Många filtreringsuppgifter definieras utifrån ordning och ett erforderligt antal rader. Det är verkligen bra att ha språkstöd i T-SQL som gör det möjligt att formulera begäran på ett sätt som liknar det sätt som du tänker på uppgiften.
Det här kapitlet börjar med filtrens logiska utformningsaspekter. Det använder sedan ett scenario för sidflyttning för att demonstrera deras optimering. Kapitlet behandlar också användningen av TOP med modifieringsanvisningar. Slutligen visar kapitlet hur TOP och OFFSET-FETCH används för att lösa praktiska problem som top N per grupp och median.
Filtren TOP och OFFSET-FETCH
Du använder TOP- och OFFSET-FETCH-filtren för att implementera filtreringskrav i dina frågor på ett intuitivt sätt. TOP-filtret är en egen funktion i T-SQL, medan OFFSET-FETCH-filtret är en standardfunktion. T-SQL började stödja OFFSET-FETCH med Microsoft SQL Server 2012. Från och med SQL Server 2014 saknar implementeringen av OFFSET-FETCH i T-SQL fortfarande ett par standardelement – intressant nog sådana som är tillgängliga med TOP. Med den nuvarande implementeringen har vart och ett av filtren funktioner som inte stöds av det andra.
Jag börjar med att beskriva de logiska konstruktionsaspekterna av TOP och tar sedan upp dem av OFFSET-FETCH.
TOP-filtret
TOP-filtret är en vanligt förekommande konstruktion i T-SQL. Dess popularitet kan förmodligen tillskrivas det faktum att dess konstruktion är så väl anpassad till hur många filtreringskrav uttrycks – till exempel ”Return the three most latest orders”. I den här begäran är ordningen för filtret baserad på orderdate, fallande, och antalet rader som du vill filtrera baserat på den här ordningen är 3.
Du anger TOP-alternativet i SELECT-listan med ett inmatningsvärde skrivet som BIGINT som anger hur många rader du vill filtrera. Du anger ordningsangivelsen i den klassiska ORDER BY-klausulen. Du använder till exempel följande fråga för att få fram de tre senaste beställningarna.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Jag fick följande utdata från den här frågan:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Istället för att ange antalet rader som du vill filtrera kan du använda TOP för att ange procenten (av det totala antalet rader i frågeresultatet). Det gör du genom att ange ett värde i intervallet 0 till 100 (skrivet som FLOAT) och lägga till nyckelordet PERCENT. I följande fråga begär du till exempel att filtrera en procent av raderna:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server avrundar uppåt det antal rader som beräknats baserat på den inmatade procenten. Till exempel är resultatet av 1 procent tillämpat på 830 rader i tabellen Orders 8,3. Genom att avrunda uppåt får du 9. Här är resultatet jag fick för den här frågan:
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
Bemärk att för att översätta inmatningsprocenten till ett antal rader måste SQL Server först räkna ut antalet rader i frågeresultatet, och detta kräver vanligtvis extra arbete.
Interessant nog är orderspecifikationen valfri för TOP-filtret. Tänk till exempel på följande fråga:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Jag fick följande resultat från den här frågan:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Valet av vilka tre rader som ska returneras är icke-deterministiskt. Detta innebär att om du kör frågan igen, utan att de underliggande uppgifterna ändras, kan du teoretiskt sett få en annan uppsättning av tre rader. I praktiken beror urvalet av rader på fysiska förhållanden som optimeringsval, val av lagringsmotor, datalayout och andra faktorer. Om du faktiskt kör frågan flera gånger, så länge dessa fysiska förhållanden inte ändras, finns det en viss sannolikhet för att du får samma resultat. Men det är viktigt att förstå principen om ”fysiskt dataoberoende” från den relationella modellen och komma ihåg att du på den logiska nivån inte har någon garanti för upprepbara resultat. Utan specifikation av ordningen bör du betrakta ordningen som godtycklig, vilket resulterar i ett icke-deterministiskt radval.
Även när du tillhandahåller specifikation av ordningen betyder det inte att frågan är deterministisk. I en tidigare TOP-fråga användes t.ex. orderdate, DESC som ordningsangivelse. Kolumnen orderdate är inte unik och därför är urvalet mellan rader med samma orderdatum icke-deterministiskt. Vad gör man då när man måste garantera determinism? Det finns två alternativ: att använda WITH TIES eller unik ordning.
Objektet WITH TIES gör att bindningar inkluderas i resultatet. Så här tillämpar du det på vårt exempel:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Här är resultatet jag fick från den här frågan:
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 filtrerar de tre raderna med de senaste beställningsdatumen och inkluderar dessutom alla andra rader som har samma beställningsdatum som i den sista raden. Som ett resultat kan du få fler rader än det antal du angav. I den här frågan angav du att du ville filtrera tre rader men fick fyra rader. Det som är intressant att notera här är att radvalet nu är deterministiskt, men att presentationsordningen mellan rader med samma beställningsdatum är icke-deterministisk.
Den andra metoden för att garantera ett deterministiskt resultat är att göra beställningsspecifikationen unik genom att lägga till en tiebreaker. Du kan till exempel lägga till orderid, DESC som tiebreaker i vårt exempel. Detta innebär att vid oavgjort i orderdatumvärdena föredras en rad med ett högre orderid-värde framför en rad med ett lägre värde. Här är vår fråga med tiebreaker tillämpad:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Denna fråga genererar följande utdata:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Användning av unik ordning gör både radvalet och presentationsordningen deterministisk. Såväl resultatmängden som presentationsordningen för raderna är garanterat upprepningsbara så länge de underliggande uppgifterna inte ändras.
Om du har ett fall där du behöver filtrera ett visst antal rader, men verkligen inte bryr dig om ordningen, kan det vara en bra idé att ange ORDER BY (SELECT NULL), på följande sätt:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
På så sätt låter du alla veta att ditt val av godtycklig ordning är avsiktligt, vilket bidrar till att undvika förvirring och tvivel.
Som en påminnelse om vad jag förklarade i kapitel 1, ”Logisk frågebearbetning”, om TOP- och OFFSET-FETCH-filtren, garanteras presentationsordningen endast om den yttre frågan har en ORDER BY-klausul. I till exempel följande frågepresentation är ordningen inte garanterad:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
För att ge en garanti för presentationsordning måste du ange en ORDER BY-klausul i den yttre frågan, så här:
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;
Filtret OFFSET-FETCH
Filtret OFFSET-FETCH är en standardfunktion som är utformad på samma sätt som TOP men med ett extra element. Du kan ange hur många rader du vill hoppa över innan du anger hur många rader du vill filtrera.
Som du kanske har gissat kan den här funktionen vara praktisk vid implementering av paging-lösningar – det vill säga att återge ett resultat till användaren en bit i taget på begäran när hela resultatuppsättningen är för lång för att rymmas på en skärm eller webbsida.
Filtret OFFSET-FETCH kräver att det finns en ORDER BY-klausul, och den anges direkt efter den. Du börjar med att ange hur många rader du vill hoppa över i en OFFSET-klausul, följt av hur många rader du vill filtrera i en FETCH-klausul. Med utgångspunkt i den angivna ordningen hoppar till exempel följande fråga över de första 50 raderna och filtrerar de följande 25 raderna:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Med andra ord filtrerar frågan raderna 51 till 75. Om man utgår från en sidstorlek på 25 rader, returnerar denna fråga den tredje sidan.
För att möjliggöra ett naturligt deklarationsspråk kan du använda nyckelordet FIRST i stället för NEXT om du vill, även om betydelsen är densamma. Att använda FIRST kan vara mer intuitivt om du inte hoppar över några rader. Även om du inte vill hoppa över några rader gör T-SQL det fortfarande obligatoriskt att ange OFFSET-klausulen (med 0 ROWS) för att undvika tvetydiga tolkningar. På samma sätt kan du i stället för att använda pluralformen av nyckelordet ROWS använda singularformen ROW i både OFFSET- och FETCH-klausulerna. Detta är mer naturligt om du behöver hoppa över eller filtrera endast en rad.
Om du är nyfiken på vad syftet med nyckelordet ONLY är, betyder det att inte inkludera band. Standard SQL definierar alternativet WITH TIES; T-SQL har dock inte stöd för det ännu. På samma sätt definierar standard-SQL alternativet PERCENT, men T-SQL stöder inte heller det ännu. Dessa två alternativ som saknas är tillgängliga med TOP-filtret.
Som nämnts kräver OFFSET-FETCH-filtret en ORDER BY-klausul. Om du vill använda en godtycklig ordning, som TOP utan en ORDER BY-klausul, kan du använda tricket med ORDER BY (SELECT NULL), på följande sätt:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
FETCH-klausulen är valfri. Om du vill hoppa över ett visst antal rader men inte begränsa hur många rader som ska returneras anger du helt enkelt ingen FETCH-klausul. Följande fråga hoppar till exempel över 50 rader men begränsar inte antalet returnerade rader:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Vad gäller presentationsordning är beteendet detsamma som med TOP-filtret; med OFFSET-FETCH garanteras presentationsordning endast om den yttersta frågan har en ORDER BY-klausul.