An important aspect of the "unknown" nature of NULL in SQL Server has been overlooked in the article and this forum. SQL Server looks at the column or variable with NULL assigned like a black box. Inside that black box there may be a value or there might not be a value, and if there is a value SQL Server can't know what it is.

In most conditional statements, like WHERE clauses and IF statements, the conditional expression will return TRUE if and only if SQL Server considers the assertion provably true. When one of the players in a conditional expression is a NULL, SQL Server says it can not prove anything, and it returns false.

The one big exception is a CHECK CONSTRAINT. With a CHECK CONSTRAINT the assertion just has to be not provably FALSE. Say we have [font="Courier New"]... MyEvenInteger Int NULL CHECK MyEvenInteger % 2 = 0[/font]. This will allow NULL, because SQL Server will say that the content of the black box *could* be even, so it cannot prove the assertion false.

This would allow rows with NULL, but if we have a query with [font="Courier New"]...WHERE MyEvenInteger % 2 = 0[/font], the query will not return the rows with NULL.