I am actually working on the migration of some of the stored procedures from Oracle to sqlserver 2008, and came across one stored procedure, which is actually used to work with one of the datastage jobs. The requirement of the stored procedure:>
is There are 22 million rows in a table and stored procedure is intended to extract the data based on the user input
for example, if the user input given is 1, the stored procedure is needs to get the first 2 million records out of the 22 million records,if the user input given 2 then the next set 2 million records(i.e from 2 mill to 4 million) are required to be fetched based on the row number and so on untill it reaches 22 million> here is the sample query.
select * from ( select /*+ FIRST_ROWS(n) */a.*, ROWNUM rnum from ( select * from t order by id) where ROWNUM <= :MAX_ROW_TO_FETCH where rnum >= :MIN_ROW_TO)_FETCH;
Any help is appreciated!