There's an intrinsic problem in using a scalar UDF to validate your country and region. Your scalar function essentially answers the "is it valid?" question for just a single pair of values - that might be fine in an "as-the-insert-happens" sense of a live system that only occasionally has a need for validation of a single row, but SQLServer design principles have a particular emphasis on thinking in terms of sets of data, rather than thinking of rows of data in a line-by-line fashion, requiring individual attention.
If you're performing this on a large table/lot of rows (let's say 100,000) then you could conceive that SQLServer will run your select query 600,000 times. It's going to be considerably slower to run more than half a million queries that pick out one item of data at a time, than it is going to be to run a query that joins together 100,000 rows (SQLServer is good at joining data efficiently) and then loops over it, checking some conditions and making changes.
There's a process you can follow that will allow SQLServer to more effectively pick apart your function and incorporate/integrate it with the main query more effectively, particularly in terms of processing the query in parallel - You can read more about it in blogs and stackexchange answers discussing swapping scalar UDFs for inline TVFs. Some relatively minor performance improvements can be had by compiling the function. There is also work afoot to improve SQLServer's ability to process scalar UDFs inline with the rest of the query, but at the moment it's potentially an absolute showstopper for the performance of your query
You also have the option of doing away with the function (or not using it in this context) and doing the cleanup all in one go (and you could encapsulate this logic in a stored procedure if you wanted) :
a_region = ra.region_code,
b_region = rb.region_code,
c_region = rc.region_code,
d_region = rd.region_code,
e_region = re.region_code,
f_region = rf.region_code
LEFT OUTER JOIN mapping.region ra WHERE t.country = ra.country AND t.a_region = ra.region_code
LEFT OUTER JOIN mapping.region rb WHERE t.country = rb.country AND t.b_region = rb.region_code
LEFT OUTER JOIN mapping.region rc WHERE t.country = rc.country AND t.c_region = rc.region_code
LEFT OUTER JOIN mapping.region rd WHERE t.country = rd.country AND t.d_region = rd.region_code
LEFT OUTER JOIN mapping.region re WHERE t.country = re.country AND t.e_region = re.region_code
LEFT OUTER JOIN mapping.region rf WHERE t.country = rf.country AND t.f_region = rf.region_code
It basically works by left joining the region table 6 times, once for each of the
- If the relation works out, then the
r*.region_code will be populated with a value (which means the
*_region column is set to the same value it currently is, i.e. a non-op).
- If it doesn't work out, then the join fails, null is present in
You could run this regularly as a periodic fix. You could also turn this into a SELECT query and run it, to identify the ones that are failed. If you were then to move those out (to another table) so your table only contains valid related records, you could then consider making
table_region(country,*_region) have multiple foreign keys to
mapping.region(country,region_code) to ensure that records cannot be inserted into table_region that don't have a valid country/region mapping. This devolves responsibility to maintaining a valid country/region pairing to the constraints mechanism of the database and it will become impossible to insert data that has an invalid country/region pairing