
USE AdventureWorks
GO

sp_control_plan_guide N'DROP', N'ForceSalesOrderByCountry'
GO

DROP PROCEDURE Sales.GetSalesOrderByCountry1
GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry1 (@Country nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader h, Sales.Customer c,
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END
GO

-- see planforce_rewrite.sql
-- and plan4_orig.xml
EXEC sp_create_plan_guide N'ForceSalesOrderByCountry',
   N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader h, Sales.Customer c,
         Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country',
   N'OBJECT',
   N'Sales.GetSalesOrderByCountry1',
   NULL,
   N'OPTION (USE PLAN
             N''<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.1079">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT h.SalesOrderID, h.OrderDate, h.Comment&#xD;&#xA;FROM Sales.SalesOrderHeader h, Sales.Customer c,&#xD;&#xA;    Sales.SalesTerritory t&#xD;&#xA;WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID&#xD;&#xA;    AND CountryRegionCode = N''''US''''&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.899876" StatementEstRows="16052.5" 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="39">
            <RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="16052.5" EstimateIO="0" EstimateCPU="0.267525" AvgRowSize="151" EstimatedTotalSubtreeCost="0.899876" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <Hash>
                <DefinedValues />
                <HashKeysBuild>
                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                </HashKeysBuild>
                <HashKeysProbe>
                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
                </HashKeysProbe>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="9592.5" EstimateIO="0" EstimateCPU="0.0400967" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0751948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                  </OutputList>
                  <NestedLoops Optimized="0">
                    <OuterReferences>
                      <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                    </OuterReferences>
                    <RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="19" EstimatedTotalSubtreeCost="0.003293" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="CountryRegionCode" />
                      </OutputList>
                      <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="CountryRegionCode" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Index="PK_SalesTerritory_TerritoryID" Alias="t" />
                        <Predicate>
                          <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[CountryRegionCode] as [t].[CountryRegionCode]=N''''US''''">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="CountryRegionCode" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="N''''US''''" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </IndexScan>
                    </RelOp>
                    <RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1918.5" EstimateIO="0.00534722" EstimateCPU="0.00226735" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0318004" Parallel="0" EstimateRebinds="4" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                      </OutputList>
                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Index="IX_Customer_TerritoryID" Alias="c" />
                        <SeekPredicates>
                          <SeekPredicate>
                            <Prefix ScanType="EQ">
                              <RangeColumns>
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="TerritoryID" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[TerritoryID] as [t].[TerritoryID]">
                                  <Identifier>
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                                  </Identifier>
                                </ScalarOperator>
                              </RangeExpressions>
                            </Prefix>
                          </SeekPredicate>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
                <RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="31465" EstimateIO="0.522384" EstimateCPU="0.0347685" AvgRowSize="155" EstimatedTotalSubtreeCost="0.557153" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
                  </OutputList>
                  <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="PK_SalesOrderHeader_SalesOrderID" Alias="h" />
                  </IndexScan>
                </RelOp>
              </Hash>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'')'

sp_control_plan_guide N'ENABLE', N'ForceSalesOrderByCountry'
GO

EXECUTE Sales.GetSalesOrderByCountry1 'US'
GO

-- no guide reference in XML plan
-- but...uses the same plan
sp_control_plan_guide N'DISABLE', N'ForceSalesOrderByCountry'
GO

EXECUTE Sales.GetSalesOrderByCountry1 'AU'
GO

