Common Table Expression
I'm wondering if someone can help explain whether my thought on the CTE below is correct. I'm using SQL to page data using the Row_Number() function. If I write a query like this:
WITH MyCTE AS (
SELECTRow_Number()OVER (ORDERBY colA) RowNumber,
SELECT TOP(50) * FROM MyCTE WHERE RowNumber > @LastRecordId
This works great as it gets the next page's data using the last row number of the previous page as the next page's parameter.
Now, if the select from "myTable" has 100000 rows, does the top 50 select from "MyCTE" limit that query once it's found the top 50 records based on the row number? Or does it retrieve the entire 100000 rows then sub query them, because that to me kind of defeats the purpose of what I'm trying to achieve - only get the records needed by the recordnumber and limited to 50 records? Is this the case?