Paging the query result

If you have 5000 products in database, you may like to show them in pages on the screen. It’s a common request to only retrieve the records on a certain page. This query must be quick and efficient if we are talking about millions of records in a table for an AJAX control.
In Oracle, we can do this:
SELECT * from
(
SELECT *, ROWNUM as rownumber from Product order by name
)
where rownumber between (PageNbr - 1)*PageSize + 1 and PageNbr*PageSize
As you can see, this query needs to fetch all rows first then narrows down the result. A more efficient query is:
select *
from ( select a.*, rownum rnum
from ( select * from product order by name) a
where rownum <= PageNbr*PageSize)
where rnum >= (PageNbr - 1)*PageSize + 1
Oracle will optimize this query, so it is not as resource intensive as the first one.
In SQL Server 2005, we can do similar query:
WITH Temp AS
(
SELECT row_number() OVER (ORDER BY [name]) AS rowNum, * from Product
)
SELECT * FROM Temp
WHERE rowNum between (@PageNbr - 1) * @PageSize + 1 and @PageNbr * @PageSize
ORDER BY [name]
The query had been optimized in SQL Server as well. - Full Post