OFFSET provides a starting row from which SQL Server needs to start fetching rows and FETCH provides the number of rows we want to fetch from the result set (or in a query). OFFSET and FETCH can be used only with an order by clause. Offset row count: It is the number of rows to skip.
Using this feature of SQL Server 2012 one can easily implement SQL Server Paging while displaying results to the client. We will take a look at a simple example and then also how you could construct a stored procedure to implement SQL paging.
OFFSET FETCH feature
OFFSET FETCH is a feature added to the ORDER BY clause beginning with the SQL Server 2012 edition. It can be used to extract a specific number of rows starting from a specific index.
Let’s go through a simple example that demonstrates how to use these features of SQL Server 2012. You can see below that the TSQL looks the same as what you write today except after the ORDER BY clause we have the OFFSET and FETCH commands. One thing to note is that you have to use an ORDER BY to use this feature.
SQL Server 2012
WITH CTE_SoldProduct
AS
(
SELECT SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)
SELECT
P.NAME
,P.Price
,SP.QNT
FROM CTE_SoldProduct SP
INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
ORDER BY SP.QNT DESC
offset 1 rows FETCH NEXT 2 ROWS ONLY;SQL Server 2008
WITH CTE_SoldProduct
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Quantity) DESC) AS num, SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)
SELECT
P.NAME
,P.Price
,SP.QNT
FROM CTE_SoldProduct SP
INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
WHERE num >=2One of the main purposes of using the OFFSET FETCH feature is to load data in chunks. Let’s imagine we have an application that executes a SQL query and needs to show the results on several pages where each page contains only 10 results (similar to the Google search engine).
One of the easiest solutions is to use the OFFSET FETCH feature to load data in chunks to prevent memory outage errors. In this section, we provide a step-by-step guide on implementing this logic within an SSIS package.
First, we must create a new Integration Services package, then declare four variables as follows:
- RowCount (Int32): Stores the total number of rows in the source table
- IncrementValue (Int32): Stores the number of rows we need to specify in the OFFSET clause (similar to @PageSize * @PageNumber in the example above)
- RowsInChunk (Int32): Specifies the number of rows in each chunk of data (Similar to @PageSize in the example above)
- SourceQuery (String): Stores the source SQL command used to fetch data
After declaring the variables, we assign a default value for the RowsInChunk variable;
Happy Coding 🙂
