Because these tables come from a mainframe, the unique clustered index comes from NEWID(). I'm still looking for a real key.
For FLAIRTR2 an index already exists on:
FY
Account
CFI
SWDN
PrimaryDoc7
GLC
Site

Similarly for CATR1:
FiscalYear
Account
CFI
SWDN11
AgencyDocNumber
Site

The SP seems to try to use the Clustered, where the Batch uses the nonclustered Index.


>
>
>First: Post FlairTR2's and CATR1's clustered ( direct ) and noClusterered ( bookmark ) index ?
>
>try
>

>SELECT  UniqueID
>FROM FLAIRData.dbo.FlairTR2 FLAIRTR2
>INNER JOIN #Temp TMP
>	ON FLAIRTR2.FiscalYear = TMP.FY
>		AND FLAIRTR2.Account = TMP.Account
>		AND FLAIRTR2.CFI = TMP.CFI
>		AND FLAIRTR2.SWDN = TMP.SWDN
>		AND FLAIRTR2.PrimaryDoc7 = TMP.PrimaryDoc
>		AND FLAIRTR2.GLC = TMP.GLC
>		AND FLAIRTR2.Site = TMP.Site
>ORDER BY UniqueID
>
>
>SELECT  clusterIndexKey
>FROM FLAIRData.dbo.CATR1 CATR1
>INNER JOIN #Temp TMP
>	ON CATR1.FiscalYear = TMP.FY
>		AND CATR1.Account = TMP.Account
>		AND CATR1.CFI = TMP.CFI
>		AND CATR1.SWDN11 = TMP.SWDN
>		AND CATR1.AgencyDocNumber = TMP.PrimaryDoc
>		AND CATR1.Site = TMP.Site
>

>
>3th try to define an not clustered index as:
>

>FiscalYear
>Account
>CFI
>SWDN
>PrimaryDoc7
>GLC
>Site
>
>and
>
>FiscalYear
>Account
>CFI
>SWDN11
>AgencyDocNumber
>Site
>
>