Klassische Filter in SQL wie ON, WHERE und HAVING basieren auf Prädikaten. TOP und OFFSET-FETCH sind Filter, die auf einem anderen Konzept beruhen: Sie geben die Reihenfolge an und wie viele Zeilen auf der Grundlage dieser Reihenfolge gefiltert werden sollen. Viele Filteraufgaben werden auf der Grundlage der Reihenfolge und einer erforderlichen Anzahl von Zeilen definiert. Es ist sicherlich gut, wenn T-SQL eine Sprachunterstützung bietet, die es Ihnen ermöglicht, die Anfrage so zu formulieren, dass sie Ihrer Vorstellung von der Aufgabe entspricht.
Dieses Kapitel beginnt mit den logischen Designaspekten der Filter. Anschließend wird anhand eines Paging-Szenarios deren Optimierung demonstriert. Das Kapitel behandelt auch die Verwendung von TOP mit Änderungsanweisungen. Abschließend wird die Verwendung von TOP und OFFSET-FETCH bei der Lösung praktischer Probleme wie Top N pro Gruppe und Median demonstriert.
Die TOP- und OFFSET-FETCH-Filter
Mit den TOP- und OFFSET-FETCH-Filtern können Sie Filteranforderungen in Ihren Abfragen auf intuitive Weise umsetzen. Der TOP-Filter ist ein proprietäres Feature in T-SQL, während der OFFSET-FETCH-Filter ein Standard-Feature ist. T-SQL unterstützt OFFSET-FETCH seit Microsoft SQL Server 2012. Ab SQL Server 2014 fehlen bei der Implementierung von OFFSET-FETCH in T-SQL noch einige Standardelemente – interessanterweise solche, die auch bei TOP verfügbar sind. Bei der aktuellen Implementierung hat jeder der Filter Fähigkeiten, die vom anderen nicht unterstützt werden.
Ich werde zunächst die logischen Designaspekte von TOP beschreiben und dann die von OFFSET-FETCH behandeln.
Der TOP-Filter
Der TOP-Filter ist ein häufig verwendetes Konstrukt in T-SQL. Seine Beliebtheit ist wahrscheinlich darauf zurückzuführen, dass sein Design so gut auf die Art und Weise abgestimmt ist, wie viele Filteranforderungen ausgedrückt werden – zum Beispiel: „Gib die drei letzten Bestellungen zurück.“ In dieser Anfrage basiert die Reihenfolge für den Filter auf orderdate, absteigend, und die Anzahl der Zeilen, die Sie auf der Grundlage dieser Reihenfolge filtern möchten, ist 3.
Sie geben die Option TOP in der SELECT-Liste mit einem als BIGINT eingegebenen Wert an, der angibt, wie viele Zeilen Sie filtern möchten. Die Spezifikation der Reihenfolge geben Sie in der klassischen ORDER BY-Klausel an. Sie verwenden beispielsweise die folgende Abfrage, um die drei neuesten Bestellungen zu erhalten:
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Ich habe die folgende Ausgabe von dieser Abfrage erhalten:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Anstatt die Anzahl der Zeilen anzugeben, die Sie filtern möchten, können Sie TOP verwenden, um den Prozentsatz (der Gesamtanzahl der Zeilen im Abfrageergebnis) anzugeben. Dazu geben Sie einen Wert im Bereich von 0 bis 100 an (als FLOAT eingegeben) und fügen das Schlüsselwort PERCENT hinzu. In der folgenden Abfrage fordern Sie beispielsweise an, ein Prozent der Zeilen zu filtern:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server rundet die Anzahl der auf der Grundlage des eingegebenen Prozentsatzes berechneten Zeilen auf. Das Ergebnis von 1 Prozent, das auf 830 Zeilen in der Tabelle Orders angewendet wird, ist beispielsweise 8,3. Wenn Sie diese Zahl aufrunden, erhalten Sie 9. Hier ist die Ausgabe, die ich für diese Abfrage erhalten habe:
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
Beachten Sie, dass SQL Server, um den eingegebenen Prozentsatz in eine Anzahl von Zeilen zu übersetzen, zuerst die Anzahl der Zeilen im Abfrageergebnis herausfinden muss, und dies erfordert normalerweise zusätzliche Arbeit.
Interessanterweise ist die Angabe der Reihenfolge für den TOP-Filter optional. Betrachten Sie zum Beispiel die folgende Abfrage:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Ich habe die folgende Ausgabe von dieser Abfrage erhalten:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
Die Auswahl, welche drei Zeilen zurückgegeben werden sollen, ist nicht deterministisch. Das heißt, wenn Sie die Abfrage erneut ausführen, ohne dass sich die zugrunde liegenden Daten ändern, könnten Sie theoretisch einen anderen Satz von drei Zeilen erhalten. In der Praxis hängt die Auswahl der Zeilen von physischen Bedingungen ab, z. B. von der Wahl der Optimierung, der Speicher-Engine, dem Datenlayout und anderen Faktoren. Wenn Sie die Abfrage mehrmals ausführen, ist die Wahrscheinlichkeit groß, dass Sie die gleichen Ergebnisse erhalten, solange sich die physischen Bedingungen nicht ändern. Es ist jedoch wichtig, das Prinzip der „physischen Datenunabhängigkeit“ des relationalen Modells zu verstehen und daran zu denken, dass es auf der logischen Ebene keine Garantie für wiederholbare Ergebnisse gibt. Ohne Angabe der Reihenfolge sollten Sie die Reihenfolge als willkürlich betrachten, was zu einer nicht-deterministischen Zeilenauswahl führt.
Selbst wenn Sie die Reihenfolge angeben, bedeutet dies nicht, dass die Abfrage deterministisch ist. Eine frühere TOP-Abfrage verwendete zum Beispiel orderdate, DESC als Ordnungsspezifikation. Die Spalte orderdate ist nicht eindeutig; daher ist die Auswahl zwischen Zeilen mit demselben Bestelldatum nicht deterministisch. Was tun Sie also in Fällen, in denen Sie Determinismus garantieren müssen? Es gibt zwei Möglichkeiten: die Verwendung von WITH TIES oder die eindeutige Sortierung.
Die Option WITH TIES bewirkt, dass Bindungen in das Ergebnis einbezogen werden. So wenden Sie sie auf unser Beispiel an:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Das Ergebnis dieser Abfrage lautet:
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 die drei Zeilen mit den jüngsten Bestelldaten und schließt alle anderen Zeilen ein, die das gleiche Bestelldatum haben wie die letzte Zeile. Daher können Sie mehr Zeilen erhalten als die von Ihnen angegebene Anzahl. In dieser Abfrage haben Sie angegeben, dass Sie drei Zeilen filtern wollten, aber Sie haben vier erhalten. Interessant ist hier, dass die Zeilenauswahl jetzt deterministisch ist, aber die Präsentationsreihenfolge zwischen Zeilen mit demselben Bestelldatum nicht deterministisch ist.
Die zweite Methode, ein deterministisches Ergebnis zu garantieren, besteht darin, die Ordnungsspezifikation durch Hinzufügen eines Tiebreakers eindeutig zu machen. In unserem Beispiel könnte man zum Beispiel orderid, DESC als Tiebreaker hinzufügen. Das bedeutet, dass im Falle von Gleichständen bei den Werten des Bestelldatums eine Zeile mit einem höheren Wert der Bestell-ID gegenüber einer Zeile mit einem niedrigeren Wert bevorzugt wird. Hier ist unsere Abfrage mit dem Tiebreaker:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Diese Abfrage erzeugt die folgende Ausgabe:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Die Verwendung der eindeutigen Reihenfolge macht sowohl die Zeilenauswahl als auch die Präsentationsreihenfolge deterministisch. Die Ergebnismenge und die Darstellungsreihenfolge der Zeilen sind garantiert wiederholbar, solange sich die zugrundeliegenden Daten nicht ändern.
Wenn Sie eine bestimmte Anzahl von Zeilen filtern müssen, Ihnen aber die Reihenfolge wirklich egal ist, könnte es eine gute Idee sein, ORDER BY (SELECT NULL) anzugeben, etwa so:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
Auf diese Weise lassen Sie jeden wissen, dass Sie eine willkürliche Reihenfolge gewählt haben, was dazu beiträgt, Verwirrung und Zweifel zu vermeiden.
Zur Erinnerung an das, was ich in Kapitel 1, „Logische Abfrageverarbeitung“, über die Filter TOP und OFFSET-FETCH erklärt habe, ist die Präsentationsreihenfolge nur garantiert, wenn die äußere Abfrage eine ORDER BY-Klausel enthält. In der folgenden Abfragedarstellung ist die Reihenfolge beispielsweise nicht garantiert:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Um die Darstellungsreihenfolge zu garantieren, müssen Sie eine ORDER BY-Klausel in der äußeren Abfrage angeben, etwa so:
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;
Der OFFSET-FETCH-Filter
Der OFFSET-FETCH-Filter ist eine Standardfunktion, die ähnlich wie TOP konzipiert ist, aber ein zusätzliches Element enthält. Sie können angeben, wie viele Zeilen Sie überspringen wollen, bevor Sie angeben, wie viele Zeilen Sie filtern wollen.
Wie Sie vielleicht schon erraten haben, kann diese Funktion bei der Implementierung von Paging-Lösungen nützlich sein, d.h. bei der Rückgabe eines Ergebnisses an den Benutzer in Teilen auf Anfrage, wenn die gesamte Ergebnismenge zu lang ist, um in einen Bildschirm oder eine Webseite zu passen.
Der OFFSET-FETCH-Filter setzt eine ORDER BY-Klausel voraus, und er wird direkt danach angegeben. Sie geben zunächst in einer OFFSET-Klausel an, wie viele Zeilen übersprungen werden sollen, und anschließend in einer FETCH-Klausel, wie viele Zeilen gefiltert werden sollen. Beispielsweise überspringt die folgende Abfrage auf der Grundlage der angegebenen Reihenfolge die ersten 50 Zeilen und filtert die nächsten 25 Zeilen:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Mit anderen Worten: Die Abfrage filtert die Zeilen 51 bis 75. Bei einer Seitengröße von 25 Zeilen gibt diese Abfrage die dritte Seite zurück.
Um eine natürliche deklarative Sprache zu ermöglichen, können Sie das Schlüsselwort FIRST anstelle von NEXT verwenden, obwohl die Bedeutung dieselbe ist. Die Verwendung von FIRST könnte intuitiver sein, wenn Sie keine Zeilen auslassen wollen. Auch wenn Sie keine Zeilen überspringen wollen, ist es in T-SQL obligatorisch, die OFFSET-Klausel (mit 0 ROWS) anzugeben, um Mehrdeutigkeiten beim Parsen zu vermeiden. Ebenso können Sie anstelle der Pluralform des Schlüsselworts ROWS die Singularform ROW sowohl in der OFFSET- als auch in der FETCH-Klausel verwenden. Dies ist natürlicher, wenn Sie nur eine Zeile auslassen oder filtern müssen.
Wenn Sie sich fragen, was der Zweck des Schlüsselworts ONLY ist, bedeutet es, dass Bindungen nicht berücksichtigt werden sollen. Standard-SQL definiert die Alternative WITH TIES; T-SQL unterstützt sie jedoch noch nicht. Ebenso ist in Standard-SQL die Option PERCENT definiert, aber auch diese wird von T-SQL noch nicht unterstützt. Diese beiden fehlenden Optionen sind mit dem TOP-Filter verfügbar.
Wie erwähnt, erfordert der OFFSET-FETCH-Filter eine ORDER BY-Klausel. Wenn Sie eine beliebige Reihenfolge verwenden wollen, wie TOP ohne ORDER BY-Klausel, können Sie den Trick mit ORDER BY (SELECT NULL) anwenden, etwa so:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Die FETCH-Klausel ist optional. Wenn Sie eine bestimmte Anzahl von Zeilen auslassen, aber nicht die Anzahl der zurückzugebenden Zeilen begrenzen wollen, geben Sie einfach keine FETCH-Klausel an. Die folgende Abfrage überspringt beispielsweise 50 Zeilen, schränkt aber die Anzahl der zurückgegebenen Zeilen nicht ein:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Bezüglich der Darstellungsreihenfolge ist das Verhalten dasselbe wie beim TOP-Filter; auch bei OFFSET-FETCH ist die Darstellungsreihenfolge nur gewährleistet, wenn die äußerste Abfrage eine ORDER BY-Klausel enthält.