Bert Wagner shows how SQL Server handles trailing spaces when comparing two strings:
The LEN() function shows the number of characters in our string, while the DATALENGTH() function shows us the number of bytes used by that string.
In this case, DATALENGTH is equal to 10. This result is due to the padded spaces occurring after the character “a” in order to fill the defined CHAR length of 10. We can confirm this by converting the value to hexadecimal. We see the value 61 (“a” in hex) followed by nine “20” values (spaces).
Click through to see what happens and why it works the way it does.