SQL Server 2012 – Paging in T-SQL Abfragen

Bisher war es nur sehr umständlich möglich die Ausgabe bestimmter Zeilen eines Resultsets zu steuern. Mit Hilfe der TOP Anweisung kann man zwar die ersten n Zeilen ausgeben, aber wie gibt man die letzten n Zeilen aus oder etwa Zeile 20 bis 30 aus? Ich denke dabei an RANK() oder an WITH Anweisungen.

Genau an dieser Stelle hat SQL 11 eine Erweiterung des ORDER BY auf Lager.  Mittels OFFSET kann die Zeile angegeben werden, ab der die Datensätze angezeigt werden sollen. FETCH NEXT gibt die Anzahl der anzuzeigenden Zeilen an.

Die komplette Syntax dafür lautet:

SYNTAX

ORDER BY 
              order_by_expression
    [ COLLATE 
              collation_name ] 
    [ ASC | DESC ] 
    [ ,...
              n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { 
              integer_constant | 
              offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {
              integer_constant | 
              fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}
            

Ob man nun ROWS oder ROW bzw. FIRST oder NEXT verwendet, bleibt egal, da diese der ANSI Kompatibilität wegen mitgeliefert werden.

Dazu nun ein simples Beispiel

Verwendung von ORDER BY … OFFSET und FETCH NEXT

--Beschränken der Ausgabezeilen
--alle Datensätzen zw 10ten und 20ten Wert
SELECT *
    FROM Orders
    ORDER BY OrderID
    OFFSET 10-1  ROWS --Offsetzähler beginnt mit 0..daher minus 1 
    FETCH NEXT 10 ROWS ONLY;

Natürlich können auch Variablen verwendet werden

Verwendung von ORDER BY … OFFSET und FETCH NEXT mit Variablen

--Verwendung von Variablen
--ab Zeile 5 bis zur 20ten Zeile
DECLARE @startzeile INT = 5
DECLARE @endzeile INT = 20


SELECT * FROM Customers
ORDER BY CompanyName
OFFSET @startzeile -1 ROWS
FETCH NEXT @endzeile -@startzeile +1 ROWS ONLY;

Ausgabe der x letzten Zeilen

In diesem Beispiel wollen wir die “vorletzten” 20 Zeilen anzeigen.

--"vorletzten" x Zeilen ausgeben
DECLARE @letzteZeile INT = (SELECT COUNT(*) FROM Customers)  --letzte Zeile
DECLARE @Zeilen INT = 5  --Anzahl der auszugebenden Zeilen
DECLARE @startzeile INT = @letztezeile -50 --ab welcher Zeile von Ende der Tabelle gemessen


SELECT * FROM Customers
ORDER BY CompanyName
OFFSET @startzeile -1 ROWS
FETCH NEXT @zeilen +1 ROWS ONLY;

Verwendung von Optimierungshinweisen bei OFFSET und FETCH NEXT

Sofern man sich sehr!! sicher ist, dass eine bestimmte Abfrage sehr häufig vorkommt und daher auf bestimmte Werte optimiert werden sollen, lassen sich Verarbeitungshinweise angeben. In diesem Beipiel geht man davon aus, dass die Abfrage meist ab Zeile 1, aber für einen unbestimmte Zeilenmenge optimiert werden soll und daher dazu die Statistiken zu Rate gezogen werden müssen.

--"vorletzten" x Zeilen ausgeben 
DECLARE @letzteZeile INT = (SELECT COUNT(*) FROM Customers) --letzte Zeile 
DECLARE @Zeilen INT = 5 --Anzahl der auszugebenden Zeilen 
DECLARE @startzeile INT = @letztezeile -50 --ab welcher Zeile von Ende der Tabelle gemessen
 
SELECT * FROM Customers ORDER BY CompanyName 
OFFSET @startzeile -1 ROWS 
FETCH NEXT @zeilen +1 ROWS ONLY
OPTION ( OPTIMIZE FOR (@Startzeile = 1, @zeilen UNKNOWN) );