Enhanced paging support (query with limit but get total count)


In MySQL they have SQLCALCFOUND_ROWS. In MSSQL there is no "native" support for it but it can be done by:

SELECT totalrows, FirstName, LastName FROM ( 
    SELECT COUNT(*) OVER () totalrows, FirstName, LastName, 
            ROW_NUMBER() OVER (ORDER BY FirstName, LastName) rn 
    FROM Person.Contact WHERE Title='Mr.' 
) x WHERE rn BETWEEN 1 AND 10 
ORDER BY FirstName, LastName;


Some scenarios make it hard to compute the Offset/Skip. In that case you want a way to request all results ".After" (new API call) the last row you displayed. Sometimes you use cursors, sometimes you have a good identifier for the rows. An abstraction for this would be helpful.

Details here:


Status: New