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) );

Related posts:

1. SQL Server 2012 – Sequences
2. SQL Server 2012 – AdventureWorks Demodatenbank installieren
3. SQL Server 2012 – ENDOFMONTH auch in SQL Server 2008
4. Installation SQL Server Denali – SQL 11
5. SQL Server 2012 – Columnstore Indizes updaten