Brent Ozar shows how SQL Server deals with trailing spaces in columns:
The mind-blowing part to me is the <> operator – that seemed utterly crazy to me.
And if you add another table, and join to it, guess what happens:
The answer might be a bit surprising.