SQL Server 2017

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

CREATE TABLE #tmp_cohellation_fun
(
		ID	INT	IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,	myValue	VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
)

-- Garbage values to represent data you don't want
INSERT INTO #tmp_cohellation_fun
SELECT	CAST(NEWID() AS VARCHAR(50))
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;

-- Sprinkle a little bit of good data
INSERT INTO #tmp_cohellation_fun
		(myValue)
VALUES	('Apple')
	,	('apple')

-- Another healthy helping of garbage that we don't care about
INSERT INTO #tmp_cohellation_fun
SELECT	CAST(NEWID() AS VARCHAR(50))
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;

-- Some more good data
INSERT INTO #tmp_cohellation_fun
		(myValue)
VALUES
		('aPple')
	,	('APPLE')
	,	('APple')

-- Final insert of garbage that we don't care about
INSERT INTO #tmp_cohellation_fun
SELECT	CAST(NEWID() AS VARCHAR(50))
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3
;

-- Create a nonclustered rowstore index
CREATE INDEX ix_myValue ON #tmp_cohellation_fun (myValue)
;

SET STATISTICS XML ON
;

-- Seek, but incorrect results
SELECT	*
FROM	#tmp_cohellation_fun
WHERE	myValue LIKE 'apple%'
;

-- Scan, with correct results
SELECT	*
FROM	#tmp_cohellation_fun
WHERE	myValue COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 'apple%'
;

-- Seek, with correct results
SELECT	*
FROM	#tmp_cohellation_fun
WHERE	myValue LIKE '[aA][pP][pP][lL][eE]%'
;

SET STATISTICS XML OFF
;

DROP TABLE IF EXISTS #tmp_cohellation_fun

ID	myValue
438978	apple

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3451.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM #tmp_cohellation_fun WHERE myValue LIKE 'apple%'" StatementId="1" StatementCompId="9" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00331207" StatementEstRows="27.3336" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x573D53C74612EC4C" QueryPlanHash="0x0158D8C0E1EA6858" 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="16" CompileTime="0" CompileCPU="0" CompileMemory="376"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="790304"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-09-02T16:13:03.36" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000008B]" Schema="[dbo]" Database="[tempdb]"></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="27.3336" EstimatedRowsRead="27.3336" EstimateIO="0.003125" EstimateCPU="0.000187067" AvgRowSize="40" EstimatedTotalSubtreeCost="0.00331207" TableCardinality="1.31693e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="5" ActualEndOfScans="1" 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="[#tmp_cohellation_fun]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Index="[ix_myValue]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'appldþ'"><Const ConstValue="'appldþ'"></Const></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'applF'"><Const ConstValue="'applF'"></Const></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp_cohellation_fun].[myValue] like 'apple%'"><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'apple%'"></Const></ScalarOperator></Intrinsic></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

ID	myValue
877956	APPLE
877957	APple
438977	Apple
877955	aPple
438978	apple

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3451.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM #tmp_cohellation_fun WHERE myValue COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 'apple%'" StatementId="2" StatementCompId="10" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="7.64212" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xECCDA4375BEAF065" QueryPlanHash="0x572928C22EE2ADFE" 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="16" CompileTime="0" CompileCPU="0" CompileMemory="256"><Warnings><PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT(varchar(50),[tempdb].[dbo].[#tmp_cohellation_fun].[myValue],0)>='applDþ'"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="790304"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-09-02T16:13:03.36" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000008B]" Schema="[dbo]" Database="[tempdb]"></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="187" CpuTime="187"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1.31693e+006" EstimateIO="6.06164" EstimateCPU="1.44878" AvgRowSize="50" EstimatedTotalSubtreeCost="7.51043" TableCardinality="1.31693e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="187" ActualCPUms="187" ActualScans="1" ActualLogicalReads="8238" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1316933" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Index="[ix_myValue]" IndexKind="NonClustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CONVERT(varchar(50),[tempdb].[dbo].[#tmp_cohellation_fun].[myValue],0) like 'apple%'"><Intrinsic FunctionName="like"><ScalarOperator><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="'apple%'"></Const></ScalarOperator></Intrinsic></ScalarOperator></Predicate></IndexScan></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.481" Build="14.0.3451.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT	*
FROM	#tmp_cohellation_fun
WHERE	myValue COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 'apple%'" StatementId="2" StatementCompId="10" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="7.64212" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xECCDA4375BEAF065" QueryPlanHash="0x572928C22EE2ADFE" 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="16" CompileTime="0" CompileCPU="0" CompileMemory="256"><Warnings><PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT(varchar(50),[tempdb].[dbo].[#tmp_cohellation_fun].[myValue],0)>='applDþ'"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="790304"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-09-02T16:13:03.36" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000008B]" Schema="[dbo]" Database="[tempdb]"></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="187" CpuTime="187"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1.31693e+006" EstimateIO="6.06164" EstimateCPU="1.44878" AvgRowSize="50" EstimatedTotalSubtreeCost="7.51043" TableCardinality="1.31693e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="187" ActualCPUms="187" ActualScans="1" ActualLogicalReads="8238" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1316933" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Index="[ix_myValue]" IndexKind="NonClustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CONVERT(varchar(50),[tempdb].[dbo].[#tmp_cohellation_fun].[myValue],0) like 'apple%'"><Intrinsic FunctionName="like"><ScalarOperator><Convert DataType="varchar" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="'apple%'"></Const></ScalarOperator></Intrinsic></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

ID	myValue
877956	APPLE
877957	APple
438977	Apple
877955	aPple
438978	apple

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3451.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM #tmp_cohellation_fun WHERE myValue LIKE '[aA][pP][pP][lL][eE]%'" StatementId="3" StatementCompId="11" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00331207" StatementEstRows="27.3336" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x573D53C74612EC4C" QueryPlanHash="0x0158D8C0E1EA6858" 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="16" CompileTime="0" CompileCPU="0" CompileMemory="376"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="790304"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-09-02T16:13:03.36" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000008B]" Schema="[dbo]" Database="[tempdb]"></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="15" CpuTime="15"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="27.3336" EstimatedRowsRead="27.3336" EstimateIO="0.003125" EstimateCPU="0.000187067" AvgRowSize="40" EstimatedTotalSubtreeCost="0.00331207" TableCardinality="1.31693e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="520" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="82385" ActualEndOfScans="1" 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="[#tmp_cohellation_fun]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Index="[ix_myValue]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'9þþþþþþþþþþþþþþþþþþþþ'"><Const ConstValue="'9þþþþþþþþþþþþþþþþþþþþ'"></Const></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'B'"><Const ConstValue="'B'"></Const></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp_cohellation_fun].[myValue] like '[aA][pP][pP][lL][eE]%'"><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'[aA][pP][pP][lL][eE]%'"></Const></ScalarOperator></Intrinsic></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan