Can comparing a nvarchar column to a varchar in the join cause duplicate rows in the result set?
I pulled the SELECT out of the MERGE to test. I found that after adding the CAST, the result set is smaller. It seems that when running the original SELECT, the result set has millions of duplicates. Could this a byproduct of comparing the nvarchar datatype to a varchar?
FYi, the top 3000000 statement was added to get the query to complete on my test machine.
Begs the question though – why do your joining columns need any casting at all? Conversion – especially when unexpected – can be quite expensive.