SQL Server 2008 pagination with total rows -

i see there similar questions, unable find answer understood not sql query expert.

this works page of records:

with page  (    select         row_number() on (order sequence_no asc) _row_, *     mytable )  select *  page  _row_ between 0 , 25 

but how can modify returns total number of records matched first query ?

with page  (    select         row_number(), count(*) on (order sequence_no asc) _row_, _total_, *     mytable )  select *  page  _row_ between 0 , 25 

i following error:

incorrect syntax near 'row_number', expected 'over'

that not right syntax, need use over clause count

try this

;with page  ( select row_number() over(order sequence_no asc) _row_,        count(*) over() _total_, *  mytable )  select * page _row_ between 0 , 25