SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table BigNumbers (
    col1 tinyint not null,
    col2 tinyint not null,
    col3 tinyint not null,

    index IX_BigNumbers unique clustered (col1, col2, col3)
)

DECLARE @n INT = 10;

DECLARE @x1 INT = 0;
DECLARE @x2 INT = 0;
DECLARE @x3 INT = 0;

SET NOCOUNT ON;

WHILE @x3 <= @n BEGIN
    SET @x2 = 0;
    WHILE @x2 <= @n BEGIN
        SET @x1 = 0;
        WHILE @x1 <= @n BEGIN
            insert into BigNumbers values (@x1, @x2, @x3);
            SET @x1 = @x1 + 1;
        END;
        SET @x2 = @x2 + 1;
    END;

 SET @x3 = @x3 + 1;
END;
set statistics xml on;
DECLARE @x1 INT = 6;
DECLARE @x2 INT = 4;
DECLARE @x3 INT = 9;

select top 5 *
from BigNumbers 
where
  col1 > @x1 or
 (col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3)))
order by col1, col2, col3


col1
col2
col3


6
4
9

6
4
10

6
5
0

6
5
1

6
5
2




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="select top 5 *&#xa;from BigNumbers &#xa;where&#xa;  col1 &gt; @x1 or&#xa; (col1 = @x1 and&#xa;   (col2 &gt; @x2 or&#xa;   (col2 = @x2 and col3 &gt;= @x3)))&#xa;order by col1, col2, col3" StatementId="1" StatementCompId="5" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00338984" StatementEstRows="5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2BA8A7A929EB8606" QueryPlanHash="0xFF335774ABBADE4A" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><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="15" CompileCPU="15" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1428344"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-08-31T13:30:11.81" ModificationCount="0" SamplingPercent="100" Statistics="[IX_BigNumbers]" Table="[BigNumbers]" Schema="[dbo]" Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-31T13:30:11.82" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[BigNumbers]" Schema="[dbo]" Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]"></StatisticsInfo></OptimizerStatsUsage><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="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="10" EstimatedTotalSubtreeCost="0.00338984" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(5)"><Const ConstValue="(5)"></Const></ScalarOperator></TopExpression><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimateRowsWithoutRowGoal="423.482" EstimatedRowsRead="1331" EstimateIO="0.00831019" EstimateCPU="0.0016211" AvgRowSize="10" EstimatedTotalSubtreeCost="0.00335665" TableCardinality="1331" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="784" 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="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Index="[IX_BigNumbers]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f].[dbo].[BigNumbers].[col1]&gt;[@x1] OR [fiddle_85dbb9c52eeb470d96a0604dad8c1c6f].[dbo].[BigNumbers].[col1]=[@x1] AND ([fiddle_85dbb9c52eeb470d96a0604dad8c1c6f].[dbo].[BigNumbers].[col2]&gt;[@x2] OR [fiddle_85dbb9c52eeb470d96a0604dad8c1c6f].[dbo].[BigNumbers].[col2]=[@x2] AND [fiddle_85dbb9c52eeb470d96a0604dad8c1c6f].[dbo].[BigNumbers].[col3]&gt;=[@x3])"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@x1"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@x1"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@x2"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@x2"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@x3"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Top></RelOp><ParameterList><ColumnReference Column="@x3" ParameterDataType="int" ParameterRuntimeValue="(9)"></ColumnReference><ColumnReference Column="@x2" ParameterDataType="int" ParameterRuntimeValue="(4)"></ColumnReference><ColumnReference Column="@x1" ParameterDataType="int" ParameterRuntimeValue="(6)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


set statistics xml on;
DECLARE @x1 INT = 6;
DECLARE @x2 INT = 4;
DECLARE @x3 INT = 9;

select top 5 *
from BigNumbers 
where col1 = @x1 and col2 = @x2 and col3 >= @x3
 or col1 = @x1 and col2 > @x2
 or col1 > @x1
order by col1, col2, col3


col1
col2
col3


6
4
9

6
4
10

6
5
0

6
5
1

6
5
2




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="select top 5 *&#xa;from BigNumbers &#xa;where col1 = @x1 and col2 = @x2 and col3 &gt;= @x3&#xa; or col1 = @x1 and col2 &gt; @x2&#xa; or col1 &gt; @x1&#xa;order by col1, col2, col3" StatementId="1" StatementCompId="5" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0033019" StatementEstRows="5" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x715267E3FC135B86" QueryPlanHash="0x03E489124F1DE724" CardinalityEstimationModelVersion="150"><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="192"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1428344"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-08-31T13:30:11.81" ModificationCount="0" SamplingPercent="100" Statistics="[IX_BigNumbers]" Table="[BigNumbers]" Schema="[dbo]" Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-31T13:30:11.82" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[BigNumbers]" Schema="[dbo]" Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]"></StatisticsInfo></OptimizerStatsUsage><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="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0033019" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(5)"><Const ConstValue="(5)"></Const></ScalarOperator></TopExpression><RelOp NodeId="1" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="5" EstimateRowsWithoutRowGoal="427.434" EstimatedRowsRead="427.434" EstimateIO="0.00460648" EstimateCPU="0.000627177" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0033014" TableCardinality="1331" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="2" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="5" 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="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Index="[IX_BigNumbers]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@x1]"><Identifier><ColumnReference Column="@x1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[@x2]"><Identifier><ColumnReference Column="@x2"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col3"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@x3]"><Identifier><ColumnReference Column="@x3"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@x1]"><Identifier><ColumnReference Column="@x1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col2"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@x2]"><Identifier><ColumnReference Column="@x2"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew><SeekPredicateNew><SeekKeys><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_85dbb9c52eeb470d96a0604dad8c1c6f]" Schema="[dbo]" Table="[BigNumbers]" Column="col1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@x1]"><Identifier><ColumnReference Column="@x1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Top></RelOp><ParameterList><ColumnReference Column="@x3" ParameterDataType="int" ParameterRuntimeValue="(9)"></ColumnReference><ColumnReference Column="@x2" ParameterDataType="int" ParameterRuntimeValue="(4)"></ColumnReference><ColumnReference Column="@x1" ParameterDataType="int" ParameterRuntimeValue="(6)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>