In Transact-SQL, system-generated NULLs, such as those that result from a system function like convert, behave differently than user-assigned NULLs. For example, in the following statement, a not equals comparison of the user-provided NULL and 1 returns TRUE:
if (1 != NULL) print "yes" else print "no"
yes
The same comparison with a system-generated NULL returns UNKNOWN:
if (1 != convert(integer, NULL)) print "yes" else print "no"
no
For more consistent behavior, set ansinull on. Then both system-generated and user-provided NULLs cause the comparison to return UNKNOWN.
