SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1
( cola bigint );

-- CREATE NONCLUSTERED INDEX NCIX_Table1_Cola ON table1(cola);
CREATE CLUSTERED INDEX NCIX_Table1_Cola ON table1(cola);

insert into table1 values(12),(145),(34),(90),(88990),(987611),(55);

create table table2
(cola bigint,
 colb bigint,
 colc varchar(10),
 cold varchar(10),
 cole varchar(10)
 );

-- CREATE NONCLUSTERED INDEX NCIX_Table2_Col_a_b ON table2(cola,colb);
-- CREATE NONCLUSTERED INDEX NCIX_Table2_Col_a_b ON table2(cola,colb) INCLUDE (colC,colD,colE);
CREATE CLUSTERED INDEX NCIX_Table2_Col_a_b ON table2(cola,colb);
-- CREATE NONCLUSTERED INDEX NCIX_Table2_Col_b_a ON table2(colb,cola);
-- CREATE NONCLUSTERED INDEX NCIX_Table2_Col_b_a ON table2(colb,cola) INCLUDE (colC,colD,colE);
-- CREATE CLUSTERED INDEX NCIX_Table2_Col_b_a ON table2(colb,cola);
-- CREATE NONCLUSTERED INDEX NCIX_Table2_Col_a ON table2(cola);
-- CREATE CLUSTERED INDEX NCIX_Table2_Col_a ON table2(cola);
-- CREATE NONCLUSTERED INDEX NCIX_Table2_Col_b ON table2(colb);
-- CREATE CLUSTERED INDEX NCIX_Table2_Col_b ON table2(colb);

insert into table2 values
 (0,10,'c1','d1','e1'),
 (11,20,'c2','d2','e2'),
 (21,40,'c3','d3','e3'),
 (41,60,'c4','d4','e4'),
 (61,100,'c5','d5','e5'),
 (101,1000,'c6','d6','e6'),
 (1001,10000,'c7','d7','e7'),
 (10001,200000,'c8','d8','e8');

15 rows affected

InputValue	cola	colb	colc	cold	cole
12	11	20	c2	d2	e2
34	21	40	c3	d3	e3
55	41	60	c4	d4	e4
90	61	100	c5	d5	e5
145	101	1000	c6	d6	e6
88990	10001	200000	c8	d8	e8

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="11.0.7001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT t1.cola AS [InputValue],t2.cola,t2.colb,t2.colc,t2.cold,t2.cole FROM table2 t2 INNER JOIN table1 t1 ON t1.cola BETWEEN t2.cola AND t2.colb ;" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00737758" StatementEstRows="8.46317" StatementOptmLevel="FULL" QueryHash="0x379C7280E32AF610" QueryPlanHash="0x7A281FD80271104C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="15" CompileCPU="0" CompileMemory="176"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209662" EstimatedPagesCached="10359" EstimatedAvailableDegreeOfParallelism="1"/><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"/><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="8.46317" EstimateIO="0" EstimateCPU="0.00023408" AvgRowSize="54" EstimatedTotalSubtreeCost="0.00737758" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colc"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cold"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cole"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table1].[cola] as [t1].[cola]>=[fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table2].[cola] as [t2].[cola] AND [fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table1].[cola] as [t1].[cola]<=[fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table2].[colb] as [t2].[colb]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="7" EstimateIO="0.003125" EstimateCPU="0.0001647" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" ActualRowsRead="7" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></DefinedValue></DefinedValues><Object Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Index="[NCIX_Table1_Cola]" Alias="[t1]" IndexKind="Clustered"/></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="8" EstimateIO="0.0032035" EstimateCPU="8.73e-005" AvgRowSize="46" EstimatedTotalSubtreeCost="0.0038146" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="6" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colc"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cold"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cole"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="56" ActualRowsRead="56" ActualEndOfScans="7" ActualExecutions="7"/></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colc"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cold"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cole"/></DefinedValue></DefinedValues><Object Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Index="[NCIX_Table2_Col_a_b]" Alias="[t2]" IndexKind="Clustered"/></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="11.0.7001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT t1.cola AS [InputValue],t2.cola,t2.colb,t2.colc,t2.cold,t2.cole
FROM table2 t2 
INNER JOIN table1 t1 ON t1.cola BETWEEN t2.cola AND t2.colb ;" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00737758" StatementEstRows="8.46317" StatementOptmLevel="FULL" QueryHash="0x379C7280E32AF610" QueryPlanHash="0x7A281FD80271104C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="15" CompileCPU="0" CompileMemory="176"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209662" EstimatedPagesCached="10359" EstimatedAvailableDegreeOfParallelism="1"/><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"/><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="8.46317" EstimateIO="0" EstimateCPU="0.00023408" AvgRowSize="54" EstimatedTotalSubtreeCost="0.00737758" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colc"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cold"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cole"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table1].[cola] as [t1].[cola]>=[fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table2].[cola] as [t2].[cola] AND [fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table1].[cola] as [t1].[cola]<=[fiddle_5ff016511ec9453ebbb82c4495b03318].[dbo].[table2].[colb] as [t2].[colb]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="7" EstimateIO="0.003125" EstimateCPU="0.0001647" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" ActualRowsRead="7" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Alias="[t1]" Column="cola"/></DefinedValue></DefinedValues><Object Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table1]" Index="[NCIX_Table1_Cola]" Alias="[t1]" IndexKind="Clustered"/></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="8" EstimateIO="0.0032035" EstimateCPU="8.73e-005" AvgRowSize="46" EstimatedTotalSubtreeCost="0.0038146" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="6" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colc"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cold"/><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cole"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="56" ActualRowsRead="56" ActualEndOfScans="7" ActualExecutions="7"/></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cola"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colb"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="colc"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cold"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="cole"/></DefinedValue></DefinedValues><Object Database="[fiddle_5ff016511ec9453ebbb82c4495b03318]" Schema="[dbo]" Table="[table2]" Index="[NCIX_Table2_Col_a_b]" Alias="[t2]" IndexKind="Clustered"/></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>