System-Generated NULLs

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, enable set ansinull (set to on), so both system-generated and user-provided NULLs cause the comparison to return unknown.