SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
if object_id('tempdb..#nums') is not null drop table #nums;

create table #nums(n int primary key);

with
  p1 as (select 0 as c union all select 0),
  p2 as (select 0 as c from p1, p1 as t),
  p4 as (select 0 as c from p2, p2 as t),
  p8 as (select 0 as c from p4, p4 as t),
  p16 as (select 0 as c from p8, p8 as t),
  p32 as (select 0 as c from p16, p16 as t),
  nums as (select row_number() over (order by (select 0)) as n from p32)
insert #nums
select top(10000000) n
from nums
where n < 4000000 or n > 4000000 + 5
order by n
--  offset 0 rows
--  fetch next 5000000 rows only
;
10000000 rows affected

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SET STATISTICS XML ON;

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


with
  a as (select n, row_number() over (order by n) rn from #nums)
select top(1) rn from a where n > rn order by n;


rn


4000000


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="with&#xa;  a as (select n, row_number() over (order by n) rn from #nums)&#xa;select top(1) rn from a where n &gt; rn order by n" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00329048" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x42A291B3A3D6899B" QueryPlanHash="0x4CD97CE919BFCF12" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="168"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1152168"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="874" CpuTime="874"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00329048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="874" ActualCPUms="874" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateRowsWithoutRowGoal="3e+006" EstimateIO="0" EstimateCPU="4.8" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00329038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Column="n"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="874" ActualCPUms="874" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="3.33333" EstimateRowsWithoutRowGoal="1e+007" EstimateIO="0" EstimateCPU="0.8" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00328878" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Column="n"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4000000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="687" ActualCPUms="687" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="3.33333" EstimateRowsWithoutRowGoal="1e+007" EstimateIO="0" EstimateCPU="0.2" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00328851" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Column="n"></ColumnReference><ColumnReference Column="Segment1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4000000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="484" ActualCPUms="484" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1003"></ColumnReference></SegmentColumn><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3.33333" EstimateRowsWithoutRowGoal="1e+007" EstimatedRowsRead="1e+007" EstimateIO="11.912" EstimateCPU="11.0002" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00328845" TableCardinality="1e+007" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Column="n"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4000000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="421" ActualCPUms="421" ActualScans="1" ActualLogicalReads="6444" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4000000" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Column="n"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Index="[PK__#nums_____3BD01993C23F7496]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Segment></RelOp></SequenceProject></RelOp><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#nums].[n]&gt;[Expr1002]"><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#nums]" Column="n"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Top></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


Table '#nums_______________________________________________________________________________________________________________000000000FE1'. Scan count 1, logical reads 6444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.