add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[MyTable]
(
[TransactionID] integer NOT NULL,
[TrackingNumber] varchar(50) NOT NULL,
EventCode varchar(12) NOT NULL,
[EventDesc] varchar(50) NOT NULL,
[EventTime] datetime NOT NULL,
[EventCountry] char(2) NULL,
[EventState] bit NOT NULL,
[EventCity] varchar(50) NULL,
[EventPostCode] varchar(20) NULL,
[SpecialOperDesc] varchar(50) NULL,
[Reference] varchar(50) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.MyTable;

CREATE NONCLUSTERED INDEX IX_EventTime_TrackNum_Reference
ON dbo.MyTable (EventTime)
INCLUDE (TrackingNumber, Reference);
SET SHOWPLAN_XML ON;
-- Original statement
SELECT
[TransactionID],
[TrackingNumber],
TRY_CAST([EventCode] as int) as EventCode,
[EventDesc] ,
[EventTime],
[EventCountry],
[EventState],
[EventCity],
[EventPostCode],
[SpecialOperDesc],
[Reference]
FROM [dbo].[MyTable]
with (index = IX_EventTime_TrackNum_Reference)
WHERE EventTime>= dateadd(d,-15,getdate())
AND LEN(TrackingNumber) >= 22
AND Reference IS NOT NULL;
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="-- Original statement&#xa;SELECT &#xa; [TransactionID],&#xa; [TrackingNumber],&#xa; TRY_CAST([EventCode] as int) as EventCode, &#xa; [EventDesc] ,&#xa; [EventTime],&#xa; [EventCountry], &#xa; [EventState],&#xa; [EventCity],&#xa; [EventPostCode],&#xa; [SpecialOperDesc], &#xa; [Reference] &#xa;FROM [dbo].[MyTable] &#xa;with (index = IX_EventTime_TrackNum_Reference)&#xa;WHERE EventTime&gt;= dateadd(d,-15,getdate()) &#xa; AND LEN(TrackingNumber) &gt;= 22 &#xa; AND Reference IS NOT NULL" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00657146" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x3350B4C2A821ED16" QueryPlanHash="0x88ED1C05F9CF46DA" 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 NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="288"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="TRY_CAST([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventCode] AS int)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2051904"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="176" EstimatedTotalSubtreeCost="0.00657146" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="TRY_CAST([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventCode] AS int)"><Convert DataType="int" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="180" EstimatedTotalSubtreeCost="0.00657136" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="ColStoreLoc1000"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="79" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="ColStoreLoc1000"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="ColStoreLoc1000"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Index="[IX_EventTime_TrackNum_Reference]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="dateadd(day,(-15),getdate())"><Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(-15)"></Const></ScalarOperator><ScalarOperator><Intrinsic FunctionName="getdate"></Intrinsic></ScalarOperator></Intrinsic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[Reference] IS NOT NULL AND len([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[TrackingNumber])&gt;=(22)"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Intrinsic FunctionName="len"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(22)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="117" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="ColumnStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Index="[c]" TableReferenceId="-1" IndexKind="Clustered" Storage="ColumnStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Column="ColStoreLoc1000"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[ColStoreLoc1000]"><Identifier><ColumnReference Column="ColStoreLoc1000"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
-- Original statement SELECT [TransactionID], [TrackingNumber], TRY_CAST([EventCode] as int) as EventCode, [EventDesc] , [EventTime], [EventCountry], [EventState], [EventCity], [EventPostCode], [SpecialOperDesc], [Reference] FROM [dbo].[MyTable] with (index = IX_EventTime_TrackNum_Reference) WHERE EventTime>= dateadd(d,-15,getdate()) AND LEN(TrackingNumber) >= 22 AND Reference IS NOT NULL
SELECT
SELECT
Cached plan size40 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0065715
Estimated Number of Rows1
Statement
-- Original statement SELECT [TransactionID], [TrackingNumber], TRY_CAST([EventCode] as int) as EventCode, [EventDesc] , [EventTime], [EventCountry], [EventState], [EventCity], [EventPostCode], [SpecialOperDesc], [Reference] FROM [dbo].[MyTable] with (index = IX_EventTime_TrackNum_Reference) WHERE EventTime>= dateadd(d,-15,getdate()) AND LEN(TrackingNumber) >= 22 AND Reference IS NOT NULL
Warnings
Type conversion in expression (TRY_CAST([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventCode] AS int)) may affect "Cardinality Estimate" in query plan choice.
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
Physical OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeRow
Estimated Operator Cost0.0000001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000001
Estimated Subtree Cost0.0065715
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size176 B
Node ID0
Output List
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TransactionID
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TrackingNumber
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCountry
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventState
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCity
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventPostCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].SpecialOperDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].Reference
Expr1003
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0000052 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0065714
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size180 B
Node ID1
Output List
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TransactionID
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TrackingNumber
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCountry
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventState
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCity
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventPostCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].SpecialOperDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].Reference
Outer References
ColStoreLoc1000
Index Seek (NonClustered)
[MyTable].[IX_EventTime_TrackNum_Re…
Cost: 50%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical OperationIndex Seek
Logical OperationIndex Seek
Estimated Execution ModeRow
StorageRowStore
Estimated Operator Cost0.0032831 (50%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size79 B
OrderedTrue
Node ID2
Output List
ColStoreLoc1000
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TrackingNumber
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].Reference
Object
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[IX_EventTime_TrackNum_Reference]
Seek Predicates
Seek Keys[1]: Start: [fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime >= Scalar Operator(dateadd(day,(-15),getdate()))
Predicate
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[Reference] IS NOT NULL AND len([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[TrackingNumber])>=(22)
Columnstore Index Scan (Clustered)
[MyTable].[c]
Cost: 50%
Columnstore Index Scan (Clustered)
Uses a supplied clustering key to lookup on a table that has a clustered index.
Physical OperationKey Lookup
Logical OperationKey Lookup
Estimated Execution ModeRow
StorageColumnStore
Estimated Operator Cost0.0032831 (50%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size117 B
OrderedTrue
Node ID4
Output List
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TransactionID
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCountry
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventState
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCity
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventPostCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].SpecialOperDesc
Object
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[c]
Seek Predicates
Seek Keys[1]: Prefix: ColStoreLoc1000 = Scalar Operator([ColStoreLoc1000])
-- Original statement with 'plan guide'
SELECT
[TransactionID],
[TrackingNumber],
TRY_CAST([EventCode] as int) as EventCode,
[EventDesc] ,
[EventTime],
[EventCountry],
[EventState],
[EventCity],
[EventPostCode],
[SpecialOperDesc],
[Reference]
FROM [dbo].[MyTable]
with (index = IX_EventTime_TrackNum_Reference)
WHERE EventTime>= dateadd(d,-15,getdate())
AND LEN(TrackingNumber) >= 22
AND Reference IS NOT NULL
OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.564" Build="16.0.4165.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="8002260" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="160" StatementSubTreeCost="11.9031" StatementText="SELECT &#xD;&#xA; [TransactionID],&#xD;&#xA; [TrackingNumber],&#xD;&#xA; TRY_CAST([EventCode] as int) as EventCode, &#xD;&#xA; [EventDesc] ,&#xD;&#xA; [EventTime],&#xD;&#xA; [EventCountry], &#xD;&#xA; [EventState],&#xD;&#xA; [EventCity],&#xD;&#xA; [EventPostCode],&#xD;&#xA; [SpecialOperDesc], &#xD;&#xA; [Reference] &#xD;&#xA;FROM [dbo].[MyTable] &#xD;&#xA;WHERE EventTime&gt;= dateadd(d,-15,getdate()) &#xD;&#xA; AND LEN(TrackingNumber) &gt;= 22 &#xD;&#xA; AND Reference IS NOT NULL" StatementType="SELECT" QueryHash="0xB7DBB4FEDE8D2D4A" QueryPlanHash="0xCC564AD75406EE42" RetrievedFromCache="false" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="360">
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="TRY_CAST([dbo].[MyTable].[EventCode] AS int)" />
</Warnings>
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419430" EstimatedPagesCached="419430" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="50124640" />
<RelOp AvgRowSize="176" EstimateCPU="5.9" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="8002260" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="11.9031">
<OutputList>
<ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="TransactionID" />
<ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber" />
<ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventDesc" />
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="-- Original statement with &apos;plan guide&apos;&#xa;SELECT &#xa; [TransactionID],&#xa; [TrackingNumber],&#xa; TRY_CAST([EventCode] as int) as EventCode, &#xa; [EventDesc] ,&#xa; [EventTime],&#xa; [EventCountry], &#xa; [EventState],&#xa; [EventCity],&#xa; [EventPostCode],&#xa; [SpecialOperDesc], &#xa; [Reference] &#xa;FROM [dbo].[MyTable] &#xa;with (index = IX_EventTime_TrackNum_Reference)&#xa;WHERE EventTime&gt;= dateadd(d,-15,getdate()) &#xa; AND LEN(TrackingNumber) &gt;= 22 &#xa; AND Reference IS NOT NULL&#xa;OPTION (USE PLAN N&apos;&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt;&#xa;&lt;ShowPlanXML xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot; Version=&quot;1.564&quot; Build=&quot;16.0.4165.4&quot; xmlns=&quot;http://schemas.microsoft.com/sqlserver/2004/07/showplan&quot;&gt;&#xa; &lt;BatchSequence&gt;&#xa; &lt;Batch&gt;&#xa; &lt;Statements&gt;&#xa; &lt;StmtSimple StatementCompId=&quot;1&quot; StatementEstRows=&quot;8002260&quot; StatementId=&quot;1&quot; StatementOptmLevel=&quot;FULL&quot; CardinalityEstimationModelVersion=&quot;160&quot; StatementSubTreeCost=&quot;11.9031&quot; StatementText=&quot;SELECT &amp;#xD;&amp;#xA; [TransactionID],&amp;#xD;&amp;#xA; [TrackingNumber],&amp;#xD;&amp;#xA; TRY_CAST([EventCode] as int) as EventCode, &amp;#xD;&amp;#xA; [EventDesc] ,&amp;#xD;&amp;#xA; [EventTime],&amp;#xD;&amp;#xA; [EventCountry], &amp;#xD;&amp;#xA; [EventState],&amp;#xD;&amp;#xA; [EventCity],&amp;#xD;&amp;#xA; [EventPostCode],&amp;#xD;&amp;#xA; [SpecialOperDesc], &amp;#xD;&amp;#xA; [Reference] &amp;#xD;&amp;#xA;FROM [dbo].[MyTable] &amp;#xD;&amp;#xA;WHERE EventTime&amp;gt;= dateadd(d,-15,getdate()) &amp;#xD;&amp;#xA; AND LEN(TrackingNumber) &amp;gt;= 22 &amp;#xD;&amp;#xA; AND Reference IS NOT NULL&quot; StatementType=&quot;SELECT&quot; QueryHash=&quot;0xB7DBB4FEDE8D2D4A&quot; QueryPlanHash=&quot;0xCC564AD75406EE42&quot; RetrievedFromCache=&quot;false&quot; SecurityPolicyApplied=&quot;false&quot;&gt;&#xa; &lt;StatementSetOptions ANSI_NULLS=&quot;true&quot; ANSI_PADDING=&quot;true&quot; ANSI_WARNINGS=&quot;true&quot; ARITHABORT=&quot;true&quot; CONCAT_NULL_YIELDS_NULL=&quot;true&quot; NUMERIC_ROUNDABORT=&quot;false&quot; QUOTED_IDENTIFIER=&quot;true&quot; /&gt;&#xa; &lt;QueryPlan CachedPlanSize=&quot;40&quot; CompileTime=&quot;1&quot; CompileCPU=&quot;1&quot; CompileMemory=&quot;360&quot;&gt;&#xa; &lt;Warnings&gt;&#xa; &lt;PlanAffectingConvert ConvertIssue=&quot;Cardinality Estimate&quot; Expression=&quot;TRY_CAST([dbo].[MyTable].[EventCode] AS int)&quot; /&gt;&#xa; &lt;/Warnings&gt;&#xa; &lt;MemoryGrantInfo SerialRequiredMemory=&quot;0&quot; SerialDesiredMemory=&quot;0&quot; GrantedMemory=&quot;0&quot; MaxUsedMemory=&quot;0&quot; /&gt;&#xa; &lt;OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant=&quot;419430&quot; EstimatedPagesCached=&quot;419430&quot; EstimatedAvailableDegreeOfParallelism=&quot;8&quot; MaxCompileMemory=&quot;50124640&quot; /&gt;&#xa; &lt;RelOp AvgRowSize=&quot;176&quot; EstimateCPU=&quot;5.9&quot; EstimateIO=&quot;0&quot; EstimateRebinds=&quot;0&quot; EstimateRewinds=&quot;0&quot; EstimatedExecutionMode=&quot;Batch&quot; EstimateRows=&quot;8002260&quot; LogicalOp=&quot;Filter&quot; NodeId=&quot;0&quot; Parallel=&quot;false&quot; PhysicalOp=&quot;Filter&quot; EstimatedTotalSubtreeCost=&quot;11.9031&quot;&gt;&#xa; &lt;OutputList&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;TransactionID&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;TrackingNumber&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;EventDesc&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;EventTime&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;EventCountry&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;EventState&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;EventCity&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;EventPostCode&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;SpecialOperDesc&quot; /&gt;&#xa; &lt;ColumnReference Database=&quot;&quot; Schema=&quot;[dbo]&quot; Table=&quot;[MyTable]&quot; Column=&quot;Reference&quot; /&gt;&#xa; &lt;ColumnReference Column=&quot;Expr1003&quot; /&gt;&#xa; &lt;/OutputList&gt;&#xa; &lt;Filter StartupExpression=&quot;false&quot;&gt;&#xa; &lt;RelOp AvgRowSize=&quot;180&quot; EstimateCPU=&quot;0.5&quot; E" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.003142" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xCC564AD75406EE42" QueryPlanHash="0xCC564AD75406EE42" 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 NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="15" CompileCPU="15" CompileMemory="480" UsePlan="1"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="TRY_CAST([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventCode] AS int)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2051904"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.18e-06" AvgRowSize="176" EstimatedTotalSubtreeCost="0.003142" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-08" AvgRowSize="180" EstimatedTotalSubtreeCost="0.00314082" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="TRY_CAST([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventCode] AS int)"><Convert DataType="int" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="len([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[TrackingNumber])"><Intrinsic FunctionName="len"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="1.581e-05" AvgRowSize="180" EstimatedTotalSubtreeCost="0.00314081" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="ColumnStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TransactionID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCode"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventDesc"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCountry"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventState"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventCity"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Index="[c]" IndexKind="Clustered" Storage="ColumnStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventTime]&gt;=dateadd(day,(-15),getdate()) AND [fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[Reference] IS NOT NULL"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="EventTime"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(-15)"></Const></ScalarOperator><ScalarOperator><Intrinsic FunctionName="getdate"></Intrinsic></ScalarOperator></Intrinsic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Expr1005]&gt;=(22) AND [fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[Reference] IS NOT NULL"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(22)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2088d47de8684993a3a8d708d82dc426]" Schema="[dbo]" Table="[MyTable]" Column="Reference"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
-- Original statement with 'plan guide' SELECT [TransactionID], [TrackingNumber], TRY_CAST([EventCode] as int) as EventCode, [EventDesc] , [EventTime], [EventCountry], [EventState], [EventCity], [EventPostCode], [SpecialOperDesc], [Reference] FROM [dbo].[MyTable] with (index = IX_EventTime_TrackNum_Reference) WHERE EventTime>= dateadd(d,-15,getdate()) AND LEN(TrackingNumber) >= 22 AND Reference IS NOT NULL OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.564" Build="16.0.4165.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="8002260" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="160" StatementSubTreeCost="11.9031" StatementText="SELECT &#xD;&#xA; [TransactionID],&#xD;&#xA; [TrackingNumber],&#xD;&#xA; TRY_CAST([EventCode] as int) as EventCode, &#xD;&#xA; [EventDesc] ,&#xD;&#xA; [EventTime],&#xD;&#xA; [EventCountry], &#xD;&#xA; [EventState],&#xD;&#xA; [EventCity],&#xD;&#xA; [EventPostCode],&#xD;&#xA; [SpecialOperDesc], &#xD;&#xA; [Reference] &#xD;&#xA;FROM [dbo].[MyTable] &#xD;&#xA;WHERE EventTime&gt;= dateadd(d,-15,getdate()) &#xD;&#xA; AND LEN(TrackingNumber) &gt;= 22 &#xD;&#xA; AND Reference IS NOT NULL" StatementType="SELECT" QueryHash="0xB7DBB4FEDE8D2D4A" QueryPlanHash="0xCC564AD75406EE42" RetrievedFromCache="false" SecurityPolicyApplied="false"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="360"> <Warnings> <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="TRY_CAST([dbo].[MyTable].[EventCode] AS int)" /> </Warnings> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419430" EstimatedPagesCached="419430" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="50124640" /> <RelOp AvgRowSize="176" EstimateCPU="5.9" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="8002260" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="11.9031"> <OutputList> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="TransactionID" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventDesc" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventTime" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventCountry" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventState" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventCity" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="Reference" /> <ColumnReference Column="Expr1003" /> </OutputList> <Filter StartupExpression="false"> <RelOp AvgRowSize="180" EstimateCPU="0.5" E
SELECT
SELECT
Cached plan size40 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.003142
Estimated Number of Rows1
Statement
-- Original statement with 'plan guide' SELECT [TransactionID], [TrackingNumber], TRY_CAST([EventCode] as int) as EventCode, [EventDesc] , [EventTime], [EventCountry], [EventState], [EventCity], [EventPostCode], [SpecialOperDesc], [Reference] FROM [dbo].[MyTable] with (index = IX_EventTime_TrackNum_Reference) WHERE EventTime>= dateadd(d,-15,getdate()) AND LEN(TrackingNumber) >= 22 AND Reference IS NOT NULL OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.564" Build="16.0.4165.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="8002260" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="160" StatementSubTreeCost="11.9031" StatementText="SELECT &#xD;&#xA; [TransactionID],&#xD;&#xA; [TrackingNumber],&#xD;&#xA; TRY_CAST([EventCode] as int) as EventCode, &#xD;&#xA; [EventDesc] ,&#xD;&#xA; [EventTime],&#xD;&#xA; [EventCountry], &#xD;&#xA; [EventState],&#xD;&#xA; [EventCity],&#xD;&#xA; [EventPostCode],&#xD;&#xA; [SpecialOperDesc], &#xD;&#xA; [Reference] &#xD;&#xA;FROM [dbo].[MyTable] &#xD;&#xA;WHERE EventTime&gt;= dateadd(d,-15,getdate()) &#xD;&#xA; AND LEN(TrackingNumber) &gt;= 22 &#xD;&#xA; AND Reference IS NOT NULL" StatementType="SELECT" QueryHash="0xB7DBB4FEDE8D2D4A" QueryPlanHash="0xCC564AD75406EE42" RetrievedFromCache="false" SecurityPolicyApplied="false"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="360"> <Warnings> <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="TRY_CAST([dbo].[MyTable].[EventCode] AS int)" /> </Warnings> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419430" EstimatedPagesCached="419430" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="50124640" /> <RelOp AvgRowSize="176" EstimateCPU="5.9" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="8002260" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="11.9031"> <OutputList> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="TransactionID" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="TrackingNumber" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventDesc" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventTime" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventCountry" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventState" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventCity" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="EventPostCode" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="SpecialOperDesc" /> <ColumnReference Database="" Schema="[dbo]" Table="[MyTable]" Column="Reference" /> <ColumnReference Column="Expr1003" /> </OutputList> <Filter StartupExpression="false"> <RelOp AvgRowSize="180" EstimateCPU="0.5" E
Warnings
Type conversion in expression (TRY_CAST([fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventCode] AS int)) may affect "Cardinality Estimate" in query plan choice.
Filter
Cost: 0%
Filter
Physical OperationFilter
Logical OperationFilter
Estimated Execution ModeBatch
Estimated Operator Cost0.0000012 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000012
Estimated Subtree Cost0.003142
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size176 B
Node ID0
Output List
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TransactionID
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TrackingNumber
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCountry
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventState
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCity
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventPostCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].SpecialOperDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].Reference
Expr1003
Predicate
[Expr1005]>=(22) AND [fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[Reference] IS NOT NULL
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
Physical OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeBatch
Estimated Operator Cost0 (0%)
Estimated I/O Cost0
Estimated CPU Cost0
Estimated Subtree Cost0.0031408
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size180 B
Node ID1
Output List
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TransactionID
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TrackingNumber
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCountry
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventState
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCity
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventPostCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].SpecialOperDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].Reference
Expr1003
Expr1005
Columnstore Index Scan (Clustered)
[MyTable].[c]
Cost: 100%
Columnstore Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
Physical OperationClustered Index Scan
Logical OperationClustered Index Scan
Estimated Execution ModeBatch
StorageColumnStore
Estimated Operator Cost0.0031408 (100%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0000158
Estimated Subtree Cost0.0031408
Estimated Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size180 B
OrderedFalse
Node ID2
Output List
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TransactionID
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].TrackingNumber
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventTime
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCountry
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventState
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventCity
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].EventPostCode
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].SpecialOperDesc
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].Reference
Object
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[c]
Predicate
[fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[EventTime]>=dateadd(day,(-15),getdate()) AND [fiddle_2088d47de8684993a3a8d708d82dc426].[dbo].[MyTable].[Reference] IS NOT NULL