What is OFFSET FETCH?
Almost everyone limits their query results by adding the TOP keyword. But what if my application doesn’t want to query the first 1000 records, but always tens of blocks, for example to display them sorted in pages? Since SQL Server 2012 there is OFFSET FETCH for this purpose.
How can you use OFFSET FETCH?
To understand how to use OFFSET FETCH, we query the Wide World Importers database for the most frequently sold products:
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
This query returns 227 lines, too many if you want to display them clearly in an application. If we only want to see the 10 most sold products, we would add a TOP(10):
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
But if we were to browse through the application, we would have to skip the first 10 products and display the second 10 products. We can use OFFSET for this, but since the use of TOP is then no longer allowed, we have to limit the number of lines via FETCH:
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;
That’s quite nice now. We could now put this into a stored procedure, parameterize the offset and enjoy it for life, but we wouldn’t be ninjas if we didn’t want to make it better and use the procedure if the developers decide to show 15 products or, worse, 10 products in the mobile app and 15 in the desktop application. Therefore we parameterize our query:
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;
For example, we could get the page size value from another table in a subquery. The front end developers will be happy and you won’t have to worry about changing the page size anymore, but can continue working on your ninja skills.
