Why the SQL Server ignore the empty space at the end automatically?

Question

I wrote a query

SELECT * FROM Users WHERE UserName = 'admin '

by mistaken typing. But I found that the result is as the same as

SELECT * FROM Users WHERE UserName = 'admin'

It seems that the empty space at the end is ignored by SQL Server automatically.

Can anybody tell me why?

FYI, the column UserName is of type nvarchar(MAX).

Answer 1

SQL Server is following the ANSI/ISO standard for string comparison.

The article How SQL Server Compares Strings with Trailing Spaces explains this in detail.

SQL Server follows the ANSI/ISO SQL-92 specification... 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.

Also, as explained in the article, if you compare with LIKE you do not get this behaviour.

Answer 2

If you are trying like this

SELECT * 
FROM Users 
WHERE UserName = 'admin '

empty spaces are ignored SQL Server. But if you try like this

SELECT * 
FROM Users 
WHERE UserName Like 'admin '

It consider the empty spaces also.

DECLARE @Person1 varchar(50)='admin'
SELECT 1  WHERE @Person1 = 'admin '
SELECT 1  WHERE @Person1 like 'admin '

The result of the above query set is