Efficient way to fetch the total number of records in SQL server during Paging

Question

When querying a table in sql server, im trying to fetch only the current page of records. However I need the total number of records that would be returned for the particular query to calculate the number of pages. How to do this efficiently without writing another query to count the records.

 WITH allentities 
         AS (SELECT Row_number() OVER (ORDER BY se.entityid ASC) AS 
                    rowid 
                    ,empid
                    ,lastname 
                    ,firstname
                     ,d.depname 

             FROM   emp e join dep d on e.depid=d.depid) 
    SELECT * 
    FROM   allentities 
    WHERE  rowid >= @pageid 
           AND rowid <= @pageid + 20 

Answer 1

If your application allows it, you can try to install and use search index to perform such queries. Search indexes have functionality to work with paging.

You can use for example http://lucene.apache.org/solr/. It can be configured to index Sql server db.

Answer 2

If you add a second ROW_NUMBER() sorting DESC rather than ASC, you can calculate the total number of records by adding the two rowcounts together and subtracting one. Any row in the rsult set will have the correct total number of rows:

 WITH allentities 
         AS (SELECT Row_number() OVER (ORDER BY se.entityid ASC) AS 
                    rowid 
                    ,empid
                    ,lastname 
                    ,firstname
                    ,d.depname 
                    ,ROW_NUMBER() OVER (ORDER BY se.entityid DESC) AS rowIdNeg
             FROM   emp e join dep d on e.depid=d.depid) 
    SELECT * 
            ,rowid + rowIdNeg - 1 as totalRecords
    FROM   allentities 
    WHERE  rowid >= @pageid 
           AND rowid <= @pageid + 20