Select total number of records from common table expression We are using common table expressions to temporarily save the search results.

  WITH Results (
                 ------
                  )
  AS
    (
          select ---
        )

 SELECT * FROM Results 

        WHERE Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount


Here i want to get the toatal number of records in the Results common table expression in a variable @RowCount. Is there any way to get it without using table variable or temp table.
No, because you can't mix assignments and result set generation

But what you can do is make the total row count into an extra column

WITH Results AS
(
    select
        ---,
        COUNT(*) OVER () AS TotalRowCount -- this
    ...
)
SELECT
   *
FROM 
   Results 
WHERE 
   Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount