title: CTE, ROW_NUMBER and ROWCOUNT
body: <p>I am trying to return a page of data and also row count of all data in one stored procedure which looks like following:</p>
  <pre><code>WITH Props AS
  (
      SELECT *,
      ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
      FROM Property
      WHERE PropertyType = @PropertyType AND ...
  )   
  
  SELECT * FROM Props 
  WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);
  </code></pre>
  <p><strong>I am unable to return the row count (highest row number).</strong></p>
  <p>I know this has already been discussed (I've seen this:
  <a href="https://stackoverflow.com/questions/1038506/efficient-way-of-getting-rowcount-from-a-query-using-row-number">Efficient way of getting @@rowcount from a query using row_number</a>) but when I add <code>COUNT(x) OVER(PARTITION BY 1)</code> in the CTE, the performance degrades and the query above that normally takes no time takes forever to execute. I reckon it's because the count is calculated for each row? I seems that I can't reuse the CTE in another query. Table Props has 100k records, CTE returns 5k records.</p>