強制執行計畫的狀況：使用 USE PLAN 強制執行 JOIN 演算法
此範例會強制 SQL Server 使用會指定將 inner join 邏輯運算子與 merge join 實體運算子用於查詢的計畫。
範例
USE tempdb;
GO
CREATE TABLE t1(i INT)
CREATE TABLE t2(j INT)
GO
SELECT * FROM t1, t2 WHERE t1.i = t2.j OPTION (USE PLAN N'
<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.938">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT * FROM t1, t2 WHERE t1.i = t2.j" StatementId="1"
StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0514796"
StatementEstRows="1" StatementOptmLevel="FULL">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true"
ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="20">
<RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="1"
EstimateIO="0.000313" EstimateCPU="0.00564738" AvgRowSize="15"
EstimatedTotalSubtreeCost="0.0514796" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OutputList>
<Merge ManyToMany="1">
<InnerSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[tempdb].[dbo].[t2].[j]=[tempdb].[dbo].[t1].[i]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.01625"
EstimateCPU="0.000100011" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0227581" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OutputList>
<MemoryFractions Input="1" Output="0.5" />
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1"
EstimateIO="0.0063285" EstimateCPU="7.96e-005" AvgRowSize="11"
EstimatedTotalSubtreeCost="0.0064081" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OutputList>
<TableScan Ordered="0" ForcedIndex="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t1]" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
<RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1"
EstimateIO="0.01625" EstimateCPU="0.000100011" AvgRowSize="11"
EstimatedTotalSubtreeCost="0.0227581" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OutputList>
<MemoryFractions Input="0.5" Output="0.5" />
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1"
EstimateIO="0.0063285" EstimateCPU="7.96e-005" AvgRowSize="11"
EstimatedTotalSubtreeCost="0.0064081" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OutputList>
<TableScan Ordered="0" ForcedIndex="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t2]" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>')