None of those worked... Thanks though

>>Because these tables come from a mainframe, the unique clustered index comes from NEWID(). I'm still looking for a real key.
>
>Very often who writes the software it doesn't plan it with the fundamental principle:
>the coherence and the invariance of the rules.
>Probably MSSQL it has different rules in to assign the costs of batch and sp.
>
>However
>
>

>-- we try to deceive it
>
>SELECT FLAIRTR2.*
>FROM FLAIRData.dbo.FlairTR2
>WHERE pk IN
> (select FLAIRTR2.pk   -- pk has to be or the key of the clustered index or it has to belong to the index that we want that SQL uses
>   FROM FLAIRData.dbo.FlairTR2 FLAIRTR2
>    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.Prima)
>
>-- we force the master JOIN table to #Temp
>SELECT FLAIRTR2.*
>FROM #Temp TMP
>  JOIN FLAIRData.dbo.FlairTR2 FLAIRTR2
>ON FLAIRTR2.FiscalYear = TMP.FY
>AND FLAIRTR2.Account = TMP.Account
>AND FLAIRTR2.CFI = TMP.CFI
>AND FLAIRTR2.SWDN = TMP.SWDN
>AND FLAIRTR2.PrimaryDoc7 = TMP.Prima
>OPTION ( FORCE ORDER )
>
>
>-- we force the FLAIRTR2 index
>SELECT FLAIRTR2.*
>FROM #Temp TMP
>  JOIN FLAIRData.dbo.FlairTR2 FLAIRTR2 WITH ( INDEX (indexname) )
>ON FLAIRTR2.FiscalYear = TMP.FY
>AND FLAIRTR2.Account = TMP.Account
>AND FLAIRTR2.CFI = TMP.CFI
>AND FLAIRTR2.SWDN = TMP.SWDN
>AND FLAIRTR2.PrimaryDoc7 = TMP.Prima
>
>

>The force solutions is static,
>and therefore it is a desperate solution.