The query has ROW_NUMBER function and I am getting only rows with certain range like 50 to 100 which will be passed through variable using CTE.

The same query if use with another logic like having one identity column it returns data in same time.

Is that like CTE wont flush out the buffer...? Or some other thing is happening