Was tut OFFSET FETCH?
Fast jeder limitiert seine Abfrage-Ergebnisse durch Hinzufügen von TOP. Aber was ist, wenn meine Anwendung nun nicht die ersten 1000 Datensätze abfragen möchte, sondern immer zehner-Blöcke, um diese beispielsweise in Seiten sortiert anzuzeigen? Dafür gibt es seit dem SQL Server 2012 OFFSET FETCH.
Wie könnt ihr OFFSET FETCH verwenden?
Um die Verwendung von OFFSET FETCH zu verstehen, fragen wir in der Wide World Importers Datenbank die am häufigsten verkauften Produkte ab:
SELECT
COUNT(*) as NumberOfSales
,si.[StockItemID] as ItemId
,si.[StockItemName] as Item
FROM [Sales].[OrderLines] AS ol
LEFT JOIN [Warehouse].[Stockitems] as si
ON ol.[StockItemID] = si.StockItemID
GROUP BY si.StockItemID, si.StockItemName
ORDER BY 1 DESC
Diese Abfrage liefert 227 Zeilen zurück, zu viele, wenn man sie in einer Anwendung übersichtlich darstellen möchte. Wollen wir nur die 10 am häufigsten verkauften Produkte sehen, würden wir ein TOP(10) hinzufügen:
SELECT TOP(10)
COUNT(*) as NumberOfSales
,si.[StockItemID] as ItemId
,si.[StockItemName] as Item
FROM [Sales].[OrderLines] AS ol
LEFT JOIN [Warehouse].[Stockitems] as si
ON ol.[StockItemID] = si.StockItemID
GROUP BY si.StockItemID, si.StockItemName
ORDER BY 1 DESC
Doch wenn wir nun in der Anwendung blättern würden, müssten wir ja die ersten 10 Produkte überspringen und die zweiten 10 Produkte anzeigen. Dafür können wir OFFSET verwenden, da die Verwendung von TOP dann aber nicht mehr erlaubt ist, müssen wir die Zeilenzahl über FETCH beschränken:
SELECT
COUNT(*) as NumberOfSales
,si.[StockItemID] as ItemId
,si.[StockItemName] as Item
FROM [Sales].[OrderLines] AS ol
LEFT JOIN [Warehouse].[Stockitems] as si
ON ol.[StockItemID] = si.StockItemID
GROUP BY si.StockItemID, si.StockItemName
ORDER BY 1 DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Das ist nun ganz schön. Wir könnten das nun in eine gespeicherte Prozedur stecken, den Offset parametrisieren und uns unseres Lebens freuen, allerdings wären wir keine Ninjas, wenn wir es nicht besser machen wollten und die Prozedur auch verwenden, wenn die Entwickler sich entscheiden doch 15 Produkte anzeigen zu wollen oder, schlimmer noch, 10 Produkte in der Mobile App und 15 in der Desktop-Anwendung. Dafür parametrisieren wir unsere Abfrage:
DECLARE @pagenumber int = 3, @pagesize int = 15
SELECT
COUNT(*) as NumberOfSales
,si.[StockItemID] as ItemId
,si.[StockItemName] as Item
FROM [Sales].[OrderLines] AS ol
LEFT JOIN [Warehouse].[Stockitems] as si
ON ol.[StockItemID] = si.StockItemID
GROUP BY si.StockItemID, si.StockItemName
ORDER BY 1 DESC
OFFSET (@pagenumber-1)*@pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;
Dabei könnten wir beispielsweise den Wert für die Seitengröße auch in einer Unterabfrage aus einer anderen Tabelle holen. Die Frontendentwickler wird’s freuen und ihr müsst euch keine Gedanken mehr machen, wenn sich die Seitengröße ändert, sondern könnt weiter an euren Ninja-Skills arbeiten.
