What is the best way to fetch records batch-wise from SQL Server

Question

Scenario: we are fetching rows from SQL Server to C#.Net console application and doing action on the retrieved data from SQL Server through stored procedure; after the action is performed the new data is stored into the MongoDB using C#-MongoDB-Driver.

Issue: There are billions of rows. My stored procedure contains query as follows:

select * from table_name

To work out some batch-wise logic there is no identity column nor any date columns or such.

Information: As of now the application is fetching the records upto 3500 - 5000 records and storing into MongoDB and then it throws an error which is as follows:

System.Runtime.InteropServices.SEHException (0x80004005): External component has thrown an exception.

Question: Can anyone suggest me some logic to work out for batch-wise read/fetch from SQL Server?

Solution 1

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

Solution 2

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)

Comments

[davmos]

The question tags would suggest they are using SQL Server 2008

[Amol M Kulkarni]

Yes best solution you had proposed, provided we are on MS SQL 2012. But we are on MS SQL 2008 & on 2008 R2.. Thanks for your time..

[Zohar Peled]

"you could use...ROW_NUMBER() OVER (ORDER BY (SELECT 0))...to try and preserve the natural order that the records are stored in." - Well, that's just wrong. There is no "natural order" - it's a misconception.

[Zohar Peled]

There's this old article in a website called 4GuysFromRolla about efficient paging in SQL Server 2000. I think it might still work in 2008. Worth giving it a shot.

[davmos]

@ZoharPeled thank-you very much! Over the years I had doubted the validity of that statement, but never got round to confirming for sure. So I have now removed it. Thanks also for the useful links, I will have a look at those :)