In regards to the query wouldnt this run using a more optimal plan

;With Cte_Newcompany(NewCompanyId)

AS

(

SELECT CompanyID

FROM new.CompanyIndex AS CI

INNER JOIN UserData.dbo.CriteriaDistribution AS CD

ON CI.SegmentNo =[CD].[SegmentNo]

AND CI.[CriteriaID] = CD.[CriteriaID]

AND CD.[CriteriaID] = 1

WHERE [LocalIdentifierID] = 1

AND isExcluded = 'FALSE'

)

Select

Count(t.companyId)

,Count(UserDataId)

From

TargetsBeforeCurrentCriterion AS T

INNER JOIN Cte_Newcompany Nc

ont.CompanyId=Nc.NewCompanyId

LEFT OUTER JOIN [UserData].dbo.[UserData] as UD

ON t.CompanyId=ud.CompanyId

AND ud.IsActive=1

where

[UD].[LocalIdentifierID] = @LocalIdentifierID

as you're getting rid of a potentially expensive IN statement and using an Inner join from a CTE, the variable is no longer on the join and in the Where clause where it really belongs.

I'm curious if this is better or worse than the original, or matches ChrisM's.