I normally agree that storing CSVs in a table is almost as bad as storing XML in a table. 😉

However, in this case, we're looking at the whole business key and not just elements embedded in the CSV. That means there may be a fairly easy work around with some very high performance available.

Using the respective formulas that you used in the ON criteria, create a persisted computed column in each table and index it. It's a pretty simple thing to do and makes for some nasty fast code.

BEFORE YOU DO THAT, THOUGH!!!! Adding a column to a table can break improperly written apps that use embedded INSERT statements that don't have an insert-column-list specified. The same goes for stored procedures.

There's a workaround for even that, though. Rename the old table by adding a suffix of your choosing to it. Create a "pass through" view using the problem columns in the SELECT list and name it the same as the original name of the table. Then add the new columns to the old table and you're done. Well, unless you want to hunt down the idiots that wrote the crap code that required the creatio of the view. 😛