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.
Here is a simple example;
SELECT * FROM [dbo].[tblSample]
order by SampleId
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
This instruct SQL Server to start from first record and return 10 records only.
Let’s write a dynamic sql to get data in batches; For example we have 7 records from database and we would like to get 3 records on each round trip;
DECLARE @RowCount INT, @RowIncrement INT, @RowsInChunk INT, @RemainingRows INT, @SourceQuery NVARCHAR(255)
DECLARE @RowIncrementValue INT, @BatchValue INT
DECLARE @SQL_Select NVARCHAR(4000);
DECLARE @RowIncrementCheck INT;
SET @RowCount = 7;
SET @RowIncrement = 0;
SET @RowsInChunk = 3;
--initial value
SET @RowIncrementValue = @RowIncrement
SET @BatchValue = @RowsInChunk
--loop through all records
WHILE (@RowIncrement <= @RowCount)
BEGIN
--prepare SQL statement
SET @SQL_Select =
'SELECT *
FROM [dbo].[tblSample]
order by SampleId
OFFSET ' + CAST(@RowIncrementValue AS NVARCHAR) + ' ROWS ' + CHAR(13) +
'FETCH NEXT ' + CAST(@BatchValue AS NVARCHAR) + ' ROWS ONLY'
--EXECUTE sp_executesql @SQL_Select
PRINT @SQL_SELECT
SET @RowIncrement = @RowIncrement + @RowsInChunk
SET @RowIncrementValue = @RowIncrement
--calculate remaining rows
SET @RemainingRows = @RowCount - @RowIncrement
PRINT 'Remaining Rows ' + CAST(@RemainingRows AS NVARCHAR)
IF (@RemainingRows <= @RowsInChunk) AND (@RowIncrement <= @RowCount)
BEGIN
--short circuit this loop
SET @BatchValue = @RemainingRows
SET @RowIncrement = @RowCount
END
END
Here is the result of 3 dynamic sql for 7 records;
SELECT *
FROM [dbo].[tblSample]
order by SampleId
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY
SELECT *
FROM [dbo].[tblSample]
order by SampleId
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY
SELECT *
FROM [dbo].[tblSample]
order by SampleId
OFFSET 6 ROWS
FETCH NEXT 1 ROWS ONLY
In the query above, OFFSET 0 is used to skip 0 rows and FETCH 3 ROWS ONLY is used to extract only 3 rows.
To get additional information about the ORDER BY clause and OFFSET FETCH feature, refer to the official documentation: Using OFFSET and FETCH to limit the rows returned.
Resources
Getting data from remote server
![Favorite Favorite](/wp-content/plugins/wp-favorite-posts/img/star.png)
![Loading Loading](/wp-content/plugins/wp-favorite-posts/img/loading.gif)