Definition of NULL values.Extract from BOL
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Particularly focus on this statement
No two null values are equal.
Now let us create a table with a single column with a UNIQUE key defined on it.
A UNIQUE key makes sure that there is always a UNIQUENESS in the values across the column on which it is defined.Since BOL states that no two NULL values are same ideally UNIQUE key should allow two NULL values in the column.Try inserting two NULL values into the column.
The result—Oops SQL Server wont allow this…..it does not seems to like two NULL values on a UNIQUE key.Definition Busted…
Infact SQL Server behaves different ways in different conditions when it comes to comparision between two NULL values.So the definition of NULL values in BOL make always not stand true.
Regards
Sachin Nandanwar
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
