select * from sales.customer

USE AdventureWorks;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO
 


set showplan_xml on
go
select * from sales.customer
go
set showplan_xml off
go

select * from sales.customer
 option ( USE PLAN N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.1068">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select * from sales.customer&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.106903" StatementEstRows="19185" StatementOptmLevel="TRIVIAL">
          <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="11">
            <RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="19185" EstimateIO="0" EstimateCPU="0.0019185" AvgRowSize="50" EstimatedTotalSubtreeCost="0.106903" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerID" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="TerritoryID" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerType" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="rowguid" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="ModifiedDate" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="AccountNumber" ComputedColumn="1" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="AccountNumber" ComputedColumn="1" />
                    <ScalarOperator ScalarString="[AdventureWorks].[Sales].[Customer].[AccountNumber]">
                      <Identifier>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="AccountNumber" ComputedColumn="1" />
                      </Identifier>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="19185" EstimateIO="0" EstimateCPU="0.0019185" AvgRowSize="50" EstimatedTotalSubtreeCost="0.104985" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="TerritoryID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="AccountNumber" ComputedColumn="1" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerType" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="rowguid" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="ModifiedDate" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="AccountNumber" ComputedColumn="1" />
                        <ScalarOperator ScalarString="isnull('AW'+[AdventureWorks].[dbo].[ufnLeadingZeros]([AdventureWorks].[Sales].[Customer].[CustomerID]),'')">
                          <Intrinsic FunctionName="isnull">
                            <ScalarOperator>
                              <Arithmetic Operation="ADD">
                                <ScalarOperator>
                                  <Const ConstValue="'AW'" />
                                </ScalarOperator>
                                <ScalarOperator>
                                  <UserDefinedFunction FunctionName="[AdventureWorks].[dbo].[ufnLeadingZeros]">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerID" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </UserDefinedFunction>
                                </ScalarOperator>
                              </Arithmetic>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Const ConstValue="''" />
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="19185" EstimateIO="0.0818056" EstimateCPU="0.0212605" AvgRowSize="41" EstimatedTotalSubtreeCost="0.103066" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerID" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="TerritoryID" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerType" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="rowguid" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="ModifiedDate" />
                      </OutputList>
                      <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="TerritoryID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="CustomerType" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="rowguid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Column="ModifiedDate" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Index="PK_Customer_CustomerID" />
                      </IndexScan>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
          <UDF ProcName="[AdventureWorks].[dbo].[ufnLeadingZeros]">
            <Statements>
              <StmtSimple StatementText="&#xD;&#xA;CREATE FUNCTION [dbo].[ufnLeadingZeros](&#xD;&#xA;    @Value int&#xD;&#xA;) &#xD;&#xA;RETURNS varchar(8) &#xD;&#xA;WITH SCHEMABINDING &#xD;&#xA;AS &#xD;&#xA;BEGIN&#xD;&#xA;    DECLARE @ReturnValue varchar(8);&#xD;&#xA;&#xD;&#xA;    SET @ReturnValue = CONVERT(varchar(8), @Value);&#xD;&#xA;   " StatementId="2" StatementCompId="4" StatementType="ASSIGN" />
              <StmtSimple StatementText=" SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;&#xD;&#xA;&#xD;&#xA;   " StatementId="3" StatementCompId="5" StatementType="ASSIGN" />
              <StmtSimple StatementText=" RETURN (@ReturnValue);&#xD;" StatementId="4" StatementCompId="6" StatementType="RETURN" />
            </Statements>
          </UDF>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>');
GO