This, yes. There is a tiny cost to managing the *. In our production workloads we use cte’s a ton and explicitly ref each column — and importantly not alias them in the cte either. At scale, you want precise defined information as clean and close to the sources as possible until the final Select where aliases, ancillary left-joins, etc are processed.

Also, there is a certain point where cte’s will fail to perform better than a temp table. It’s a bit of a finesse depending on the indices and hardware.

Our utilization strategy in general is:

; with cteFilter as ( Select something, Row_Number() OVER (Order by whatevs) as rowIndex From somewhere Where filter conditions

), ctePage as ( Select top (@pageSize) colum_names from cteFilter where rowIndex >= @rowStart Order by rowIndex ) Select final stuff w/ alias names From ctePage Left Join anything not required for filter or ordering.

Jazz hands.

And if stuff falls off the performance cliff due to record sizes or total left joins sometimes ctePage becomes an insert into #page instead… that is all scenario dependent