title: Efficient way to fetch the total number of records in SQL server during Paging
body: <p>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.</p>
  
  <pre><code> 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 &gt;= @pageid 
             AND rowid &lt;= @pageid + 20 
  </code></pre>