What is the best way to fetch records batch-wise from SQL Server
If you use MSSQL 2012 try OFFSET-FETCH clause. It is the best solution!
Example: SELECT … ORDER BY orderid OFFSET 25 ROWS
fetches only the next 25 rows.
It means this query will return from 25 to 50 records. The ORDER BY
clause is mandatory, so if you don't want to use order, use ORDER BY (SELECT NULL)
If you can't use OFFSET-FETCH
in SQL Server 2012 and assuming the table has a primary key or column(s) that allow you to uniquely identify a row, lets call it UniqueKey
, then in 2005 upwards you could use ROW_NUMBER
like this...
SELECT UniqueKey, col2, col3
FROM
(
SELECT UniqueKey, col2, col3, ROW_NUMBER() OVER (ORDER BY UniqueKey) AS RowNum
FROM YourTable
) sub
WHERE sub.RowNum BETWEEN @startRow AND @endRow