Change Null handling in SQL Server - by Stamey

Status : 

  Duplicate<br /><br />
		This item appears to be a duplicate of another existing Connect or internal item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 323918 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 1/24/2008 4:40:36 AM
Access Restriction Public


For years now I have been tripping over Nulls in my field values. I have read much documentation on this subject and cannot see the sense in how Nulls are handled in SQL Server. Current rules include Null does not equal Null. Also, while Null certainly does not equal 'Y', the query engine does not see it that way.
I realize that Null is an unassigned value or an unknown value, but that does not mean that Null cannot equal Null. In the human world if a field on a piece of paper does not have anything in it, and another field on a piece of paper does not have anything it in they are both Null, and consequently are the same value, unknown/unassigned. As humans we would state that they are the same thing, blank.
Sign in to post a comment.
Posted by Randy in Marin on 11/15/2011 at 11:15 AM
Why is this still active? And why is not 286422 - the solution - active?
Posted by Microsoft on 3/25/2010 at 1:38 PM
Resolved the request as duplicate.

Posted by Microsoft on 3/25/2010 at 12:31 PM
I have resolved your request as duplicate of feedback item below:

We will not change the NULL semantics in the engine but the IS DISTINCT predicate provides ability to handle NULL comparisons in a standard conformant manner.

Umachandar, SQL Programmability Team
Posted by Mark Yudkin on 2/4/2008 at 12:41 AM
The current behaviour conforms to the ANS standard subset implemented by SQL Server and is therefore correct by definition.

However, ANS/ISO SQL 99 defines an IS DISTINCT FROM predicate (SQL 99 Feature T151) and ANS 2003 extends this to include IS NOT DISTINCT FROM (SQL 2003 Feature T152). Implementation of these features of the SQL standard would address the OP's requirement for an (in)equality operator that considers NULLs to be equal, as well as helping the rest of us.
Posted by Microsoft on 1/29/2008 at 4:29 PM
Thanks for the valuable feedback.

The current SQL Server behavior regarding null handling is designed based on the ANSI SQL Standard which is the primary principal which we have been following for any SQL/T-SQL feature. According to the standard, Null is a value and cannot be represented by a literal. The regular comparison operators (i.e. equal, not equal, less than, great than) cannot be applied to it, and you should use IS [NOT] NULL predicate instead. Null doesn't mean empty string or zero.

On the other hand, SQL Server does provide a flexible option 'SET ANSI_NULLS OFF' to allow specifying standard compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

Posted by Steve Kass on 1/26/2008 at 1:55 PM
For decades, hundreds if not thousands of incredibly smart people have worked out the best way for SQL to work, and they have published consistent, thorough standards on the matter. The current NULL behavior is the right one. It may not be intuitive, but it is the way it has to be for the language to be useful.
Posted by Bob Fazio on 1/24/2008 at 10:08 AM

Null should NEVER equal NULL. There are lots of posts and blogs on this subject. And it isn't just SQL Server. You can change how NULLS are handled to go back to the NON-ANSI standard.
So if you really want NULL to equal NULL look up the option ANSI_NULLS