SQL string comparision using IF

Question

This may be a silly question, but can anyone explain why SQL returns 'False' for

IF 'test' = ' test' -- notice leading space
 SELECT 'True'
ELSE
 SELECT 'False'

but returns 'True' for

IF 'test' = 'test ' -- notice trailing space
 SELECT 'True'
ELSE
 SELECT 'False'

Edit:

I'm using SQL Server 2008 R2

Answer 1

Trailing spaces are ignored.

If you want to really test whether they're the same, so something like this:

DECLARE @foo nvarchar(50) = 'foo'
DECLARE @foo2 nvarchar(50) = 'foo ' -- trailing space

IF @foo = @foo2 AND DATALENGTH(@foo) = DATALENGTH(@foo2) --LEN ignores trailing spaces
    SELECT 'true'
ELSE
    SELECT 'false'

WHY your example is true:

http://www.timvw.be/2013/04/27/the-curious-case-of-trailing-spaces-in-sql/

As per http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt:

 3) The comparison of two character strings is determined as fol-
        lows:

        a) If the length in characters of X is not equal to the length
          in characters of Y, then the shorter string is effectively
          replaced, for the purposes of comparison, with a copy of
          itself that has been extended to the length of the longer
          string by concatenation on the right of one or more pad char-
          acters, where the pad character is chosen based on CS. If
          CS has the NO PAD attribute, then the pad character is an
          implementation-dependent character different from any char-
          acter in the character set of X and Y that collates less
          than any string under CS. Otherwise, the pad character is a
          .

        b) The result of the comparison of X and Y is given by the col-
          lating sequence CS.

        c) Depending on the collating sequence, two strings may com-
          pare as equal even if they are of different lengths or con-
          tain different sequences of characters. When the operations
          MAX, MIN, DISTINCT, references to a grouping column, and the
          UNION, EXCEPT, and INTERSECT operators refer to character
          strings, the specific value selected by these operations from
          a set of such equal values is implementation-dependent.

Answer 2

I guess this is because the database truncates trailing spaces, but not leading spaces.

Some documentation to support that guess.

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.