SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T (
  id1 int,
  id2 int,
  someValue bit not null,
  primary key (id1, id2),
  index ix (someValue)
);

INSERT T VALUES
(1, 1, 0),
(1, 2, 1),
(2, 1, 0),
(2, 2, 0);

UPDATE STATISTICS T WITH FULLSCAN;
4 rows affected

set statistics xml on;
SELECT *
FROM T
WHERE someValue = 1
ORDER BY id1, id2;

id1
id2
someValue

1
2
True

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x0900EA3E105EE2FA66ECA5172D84BFFE8F960000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x0A9CA36655584DA5" QueryPlanHash="0x3516E35CEFAEBAAE" CardinalityEstimationModelVersion="160"><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="120"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.44" ModificationCount="0" SamplingPercent="100" Statistics="[ix]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[ix]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@1]"><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

SELECT *
FROM T
WHERE someValue = 1
ORDER BY id2, id1;

id1
id2
someValue

1
2
True

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x09003E10D6025AC400D6125C74ADED7F2EC10000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="false" StatementSubTreeCost="0.0146444" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2BE8C280E057EAD6" QueryPlanHash="0xD9DA09624608DB1C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="144"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="665544" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.44" ModificationCount="0" SamplingPercent="100" Statistics="[ix]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100016" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[ix]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Sort></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

CREATE INDEX IX ON T (someValue, id2, id1) WITH (DROP_EXISTING=ON)

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="insert [dbo].[T] select * from [dbo].[T] option (maxdop 1)" StatementId="1" StatementCompId="1" StatementType="INSERT" StatementSqlHandle="0x09003CC291C1BA32149825D16F29A1E4C56C0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="false" StatementSubTreeCost="0.0246642" StatementEstRows="4" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x67C9CED2F80738A3" QueryPlanHash="0x68E0A6953BB79CE6" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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" MemoryGrant="1024" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="128"><Warnings><MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantWarning></Warnings><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="665544" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></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="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Index Insert" LogicalOp="Insert" EstimateRows="4" EstimateIO="0.01" EstimateCPU="4e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0246642" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><CreateIndex><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[IX]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="4" EstimateIO="0.0112613" EstimateCPU="0.000112496" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0146602" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="RowRefSrc1007"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="832" OutputMemoryGrant="1024" UsedMemoryGrant="16" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[ix]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></CreateIndex></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

SELECT *
FROM T
WHERE someValue = 1
ORDER BY id1, id2;

id1
id2
someValue

1
2
True

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC" StatementId="1" StatementCompId="0" StatementType="SELECT" StatementSqlHandle="0x0900FF71E9F432FEAB29E1295E3D6DF62F350000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="false" StatementSubTreeCost="0.0032864" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0A9CA36655584DA5" QueryPlanHash="0x221E54358B8008D8" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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="136"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.46" ModificationCount="0" SamplingPercent="100" Statistics="[IX]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[PK__T__21951B9C9F834CFB]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[someValue]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

SELECT *
FROM T
WHERE someValue = 1
ORDER BY id2, id1;

id1
id2
someValue

1
2
True

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC" StatementId="1" StatementCompId="0" StatementType="SELECT" StatementSqlHandle="0x0900263E1184C192A8940324A9FA43A88B140000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x2BE8C280E057EAD6" QueryPlanHash="0xE8300CEA8D1E210C" CardinalityEstimationModelVersion="160"><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="120"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.46" ModificationCount="0" SamplingPercent="100" Statistics="[IX]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[IX]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@1]"><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>