How can I reuse a Common Table Expression

If you do not require them in 2 different queries, you can try

;with query as (  Select Row_Number() over (Order By UserID ASC) as TableRowNum,         FirstName,         LastName  From   Users),totalCount AS (    SELECT COUNT(1) Total FROM query)Select  query.*,        Totalfrom    query, totalCount where   TableRowNum between 1 and 25 Order By TableRowNum ASC

If you do require 2 different queries, rather use a table var

DECLARE @User TABLE(        TableRowNum INT,        FirstName VARCHAR(50),        LastName VARCHAR(50));with query as (  Select Row_Number() over (Order By UserID ASC) as TableRowNum,         FirstName,         LastName  From   Users)INSERT INTO @UserSELECT  TableRowNum,        FirstName,        LastNameFROM    querySELECT  *FROM    @Userwhere   TableRowNum between 1 and 25 Order By TableRowNum ASCSELECT COUNT(1) FROM @User

You can do that like this :

with query as (   Select  COUNT (*) OVER (PARTITION BY 1) AS TableTotalRows, Row_Number() over (Order By OrderNum ASC) as TableRowNum,     FirstName,     LastName  From   Users)

According to Microsoft in this link:

A CTE can reference itself and previously defined CTEs in the same WITH clause.

In that new CTE referencing the previous defined CTE, we can make the count query:

;with query as (  Select Row_Number() over (Order By UserID ASC) as TableRowNum,         FirstName,         LastName  From   Users),totalCount AS (    SELECT COUNT(1) Total FROM query)Select  query.*,        Totalfrom    query, totalCount where   TableRowNum between 1 and 25 Order By TableRowNum ASC

'query' is the main CTE and 'totalCount' is using it for get the total rows count

Microsoft should have an example for a common task like this.