Does SQL Server automatically trim nvarchar fields upon query?

Question

I have this query :

select '[' + p.Firstname + ']' from Person p
where p.Firstname = 'Johanne'

In the table, I have multiple personne who have this firstname, and some have a trailing space on the value (bad insertion of the values, it will be corrected).

Why then does this query bring me this result (I inserted the brackets to visualize the spaces) :

[Johanne]
[Johanne ]
[Johanne ]
[Johanne]

Is this a configuration thing ? The real query comes from entity framework 6, but this example does it also. How can I prevent it ?

Thanks !

Edit: I could make it work using EF6 and the System.Data.Entity.SqlServer.SqlFunctions.DataLength method like this:

ctx.Person.FirstOrDefault(p => p.FirstName == "Johanne" && SqlFunctions.DataLength(p.FirstName) == "Johanne".Length);

Answer 1

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.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

See: https://support.microsoft.com/en-us/topic/inf-how-sql-server-compares-strings-with-trailing-spaces-b62b1a2d-27d3-4260-216d-a605719003b0