I guess the different results are because the [tempdb] is always using the collations as set at instance level. So as soon as the queryplan decides to use the [tempdb] (or when temporary objects are used) you will run into the difference in collation. To avoid issues when the database is set to a different cololation you always need to specify the collation you want to use in the compare.