SQL Server 2019 | db<>fiddle

clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?

≡

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. 1811910 fiddles created (25299 in the last week).

SET SHOWPLAN_XML ON;

✓

hidden batch(es)

-- Join eliminated SELECT SOD.ProductID FROM Sales.SalesOrderDetail AS SOD LEFT JOIN Production.Product AS P ON P.ProductID = SOD.ProductID;

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Join eliminated
SELECT SOD.ProductID 
FROM Sales.SalesOrderDetail AS SOD
LEFT JOIN Production.Product AS P
 ON P.ProductID = SOD.ProductID" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.336731" StatementEstRows="121317" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x8CB2A7F1819B18CE" QueryPlanHash="0x8CD1C2E322C9B309" CardinalityEstimationModelVersion="150"><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="16" CompileTime="47" CompileCPU="15" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1060936"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="121317" EstimatedRowsRead="121317" EstimateIO="0.203125" EstimateCPU="0.133606" AvgRowSize="11" EstimatedTotalSubtreeCost="0.336731" TableCardinality="121317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail_ProductID]" Alias="[SOD]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

…

hidden batch(es)

-- Join not eliminated projecting from the preserved side of the join SELECT P.ProductID FROM Sales.SalesOrderDetail AS SOD LEFT JOIN Production.Product AS P ON P.ProductID = SOD.ProductID;

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Join not eliminated projecting from the preserved side of the join
SELECT P.ProductID 
FROM Sales.SalesOrderDetail AS SOD
LEFT JOIN Production.Product AS P
 ON P.ProductID = SOD.ProductID" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.623015" StatementEstRows="121317" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xE00D29840937B23D" QueryPlanHash="0xAEAB1663B595F96E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><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="24" CompileTime="0" CompileCPU="0" CompileMemory="320"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1060936"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-07-09T18:11:20.83" ModificationCount="0" SamplingPercent="100" Statistics="[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:20.35" ModificationCount="0" SamplingPercent="100" Statistics="[PK_Product_ProductID]" Table="[Product]" Schema="[Production]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:23.49" ModificationCount="0" SamplingPercent="100" Statistics="[IX_SalesOrderDetail_ProductID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:23.36" ModificationCount="0" SamplingPercent="100" Statistics="[AK_SalesOrderDetail_rowguid]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Right Outer Join" EstimateRows="121317" EstimateIO="0" EstimateCPU="0.273556" AvgRowSize="11" EstimatedTotalSubtreeCost="0.623015" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_96478c3ecea04d37a060101b747a4b2c].[Production].[Product].[ProductID] as [P].[ProductID]=[fiddle_96478c3ecea04d37a060101b747a4b2c].[Sales].[SalesOrderDetail].[ProductID] as [SOD].[ProductID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="504" EstimatedRowsRead="504" EstimateIO="0.0120139" EstimateCPU="0.0007114" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0127253" TableCardinality="504" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Index="[PK_Product_ProductID]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="121317" EstimatedRowsRead="121317" EstimateIO="0.203125" EstimateCPU="0.133606" AvgRowSize="11" EstimatedTotalSubtreeCost="0.336731" TableCardinality="121317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail_ProductID]" Alias="[SOD]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

…

hidden batch(es)

-- "Fun" rewrite SELECT p.[Name] FROM Production.Product AS P RIGHT JOIN Sales.SalesOrderDetail AS SOD JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SOD.SalesOrderID ON SOD.ProductID = P.ProductID;

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- "Fun" rewrite
SELECT p.[Name]
FROM Production.Product AS P
RIGHT JOIN Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
 ON SOH.SalesOrderID = SOD.SalesOrderID
 ON SOD.ProductID = P.ProductID" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.623015" StatementEstRows="121317" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x98FD93F94B76F481" QueryPlanHash="0xAEAB1663B595F96E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><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="24" CompileTime="14" CompileCPU="14" CompileMemory="496"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1060936"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-07-09T18:11:20.83" ModificationCount="0" SamplingPercent="100" Statistics="[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:20.35" ModificationCount="0" SamplingPercent="100" Statistics="[PK_Product_ProductID]" Table="[Product]" Schema="[Production]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:23.49" ModificationCount="0" SamplingPercent="100" Statistics="[IX_SalesOrderDetail_ProductID]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:23.36" ModificationCount="0" SamplingPercent="100" Statistics="[AK_SalesOrderDetail_rowguid]" Table="[SalesOrderDetail]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:20.91" ModificationCount="0" SamplingPercent="100" Statistics="[PK_SalesOrderHeader_SalesOrderID]" Table="[SalesOrderHeader]" Schema="[Sales]" Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Right Outer Join" EstimateRows="121317" EstimateIO="0" EstimateCPU="0.273556" AvgRowSize="61" EstimatedTotalSubtreeCost="0.623015" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name"></ColumnReference></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_96478c3ecea04d37a060101b747a4b2c].[Sales].[SalesOrderDetail].[ProductID] as [SOD].[ProductID]=[fiddle_96478c3ecea04d37a060101b747a4b2c].[Production].[Product].[ProductID] as [P].[ProductID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="504" EstimatedRowsRead="504" EstimateIO="0.0120139" EstimateCPU="0.0007114" AvgRowSize="65" EstimatedTotalSubtreeCost="0.0127253" TableCardinality="504" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Production]" Table="[Product]" Index="[PK_Product_ProductID]" Alias="[P]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="121317" EstimatedRowsRead="121317" EstimateIO="0.203125" EstimateCPU="0.133606" AvgRowSize="11" EstimatedTotalSubtreeCost="0.336731" TableCardinality="121317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Alias="[SOD]" Column="ProductID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_96478c3ecea04d37a060101b747a4b2c]" Schema="[Sales]" Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail_ProductID]" Alias="[SOD]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

…

hidden batch(es)