Merry-go-round scanning is one of these illusionary creatures that people have heard of, but far from all have been able to actually detect.
In your case, SQL Server could be smart and say "Know what, logically, there is no need to start over from the beginning".
I tried rearranging the query like this:
set statistics io on
WAITFOR DELAY '00:00:00.2'
declare @time datetime2 = getdate()
print @time
declare @i int
; with numbering AS (
select id, rowno = row_number() OVER(ORDER BY (SELECT 1)) from test
)
SELECT id FROM numbering WHERE rowno = 10000000
print @i
set @time = getdate()
print @time
My lack of patience also compelled me to reduce the number of rows by slashing a zero.
My feeble attempt to run them in parallel did succeed very well, I got back 50382 in both windows.
One way to set up test like this is to use application locks. First take an exclusive application lock in one window and go other to the other window, where you take a shared application lock on the same resource and directly after this you run your test. Go back to the first window and in the same execution release the application lock and run the test.