SrcName (10/4/2013)
if the logic is like this then you may use RIGHT (not in where becouse there is performance problem)you can try on this way:
select * from TABLE1 t join
( select *, right(code,1)code form TABLE2 )q on t.id = q.code and t.name = q.name
or
select *, right(code,1)code into #temp form TABLE2
and after that you can join two tables.
You might be just following ideas to improve performance without knowing the complete part. Functions on the WHERE clause and on the ON clause for a JOIN. The reason is because SQL Server won't be able to use indexes but there's no way that an index might be helpful for this join.
Your second suggestion implies that you have to copy table2 into a temp table. That will have an overhead for the extra read and write for the whole table and again, no index gain.