The online documentation defines nullif() as a function which returns NULL if the argument values are equal. But why does it return NULL even if the argument values are not equal, as in the case below:
select nullif(convert(varchar(10), ' '), convert(varchar(10), ''))
----------
NULL
(1 row affected)
SQL server ignores trailing spaces when performing comparisons. So ' ' is considered the same as ''. The former is the latter with trailing spaces.
The character constant ' ' is not necessarily equivalent to a 1 character length string containing a space. Certainly in my SQL Server 2016 instance here select len(convert(varchar(10), ' ')) returns a value of 0.
The character constant ' ' is being implicitly RTRIMed by the database, including in the left hand side of your comparison.
I don't know if this is configurable behaviour, and as per usual for Microsoft documentation I'm struggling to find this behaviour documented in writing, but that's what I'm observing.
You're statement is equivalent to SELECT NULLIF('','')
the value is defined as varchar and varchar always ignore trailing spaces until ansi passing settings are not changed at session level. try to change varchar to char and see the difference.
