You're not using this in any other place in the query? I set up a test:

SELECT CASE cr.CountryRegionCode ---Mod # 2 Start

WHEN 'USA' THEN ''

WHEN 'US' THEN ''

WHEN 'CAN' THEN CASE sp.StateProvinceCode -- Nasted Case

WHEN 'AB' THEN 'CA-AB'

WHEN 'BC' THEN 'CA-BC'

ELSE ''

END

WHEN 'CA' THEN CASE sp.StateProvinceCode -- Nasted Case

WHEN 'AB' THEN 'CA-AB'

WHEN 'BC' THEN 'CA-BC'

WHEN 'CD' THEN 'CA-CD'

ELSE ''

END

END,

a.AddressLine1

FROM Person.Address AS a

JOIN Person.StateProvince AS sp

ON sp.StateProvinceID = a.StateProvinceID

JOIN Person.CountryRegion AS cr

ON cr.CountryRegionCode = sp.CountryRegionCode

WHERE a.AddressLine1 LIKE '1%';

I'm getting a clean index seek on the Address table and the function didn't affect performance. But, it can depend on where the Compute Scalar operation occurs within the query plan. I'd suggest checking there and seeing what's happening. This function, while a little odd, shouldn't, by itself, hurt performance too much. It's possible that the function is occurring prior to some additional filtering in your query as opposed to the end of the process in mine. But it's more likely that if this is for sure the performance bottleneck, you're using it in the ORDER BY statement or something, possibly leading to table scans.