clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 635472 fiddles created (13105 in the last week).

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.3192.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#x9;*&#xa;FROM&#x9;#tmp_cohellation_fun&#xa;WHERE&#x9;myValue LIKE &apos;apple%&apos;" 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="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1003816"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-11-20T17:12:48.60" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000009E]" 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="&apos;appldþ&apos;"><Const ConstValue="&apos;appldþ&apos;"></Const></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;applF&apos;"><Const ConstValue="&apos;applF&apos;"></Const></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp_cohellation_fun].[myValue] like &apos;apple%&apos;"><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;apple%&apos;"></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.3192.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#x9;*&#xa;FROM&#x9;#tmp_cohellation_fun&#xa;WHERE&#x9;myValue COLLATE SQL_Latin1_General_CP1_CI_AS LIKE &apos;apple%&apos;" 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="264"><Warnings><PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT(varchar(50),[tempdb].[dbo].[#tmp_cohellation_fun].[myValue],0)&gt;=&apos;applDþ&apos;"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1003816"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-11-20T17:12:48.60" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000009E]" 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="156" CpuTime="156"></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="156" ActualCPUms="156" 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 &apos;apple%&apos;"><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="&apos;apple%&apos;"></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.3192.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#x9;*&#xa;FROM&#x9;#tmp_cohellation_fun&#xa;WHERE&#x9;myValue LIKE &apos;[aA][pP][pP][lL][eE]%&apos;" 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="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1003816"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-11-20T17:12:48.60" ModificationCount="0" SamplingPercent="100" Statistics="[ix_myValue]" Table="[#tmp_cohellation_fun________________________________________________________________________________________________00000000009E]" 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="518" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="82155" 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="&apos;9þþþþþþþþþþþþþþþþþþþþ&apos;"><Const ConstValue="&apos;9þþþþþþþþþþþþþþþþþþþþ&apos;"></Const></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;B&apos;"><Const ConstValue="&apos;B&apos;"></Const></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp_cohellation_fun].[myValue] like &apos;[aA][pP][pP][lL][eE]%&apos;"><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp_cohellation_fun]" Column="myValue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[aA][pP][pP][lL][eE]%&apos;"></Const></ScalarOperator></Intrinsic></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
 hidden batch(es)