Treat NULL as Standard SQL NULL instead 'unknown value' - by José Guimarães

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<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 514312 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/25/2009 3:23:36 AM
Access Restriction Public


This BUG came from all version of SQL Server. Any platform! Any Language! Any OS!

On SQL Definition the NULL means it is no value, but the SQL Server 2008 consider as 'unknown' value.

In BOL a value of NULL indicates that the value is unknown. It is a wrong SQL definition!!!

Standard SQL NULL means 'NO VALUE' or 'not exists' and not 'unknown value'!

NULL should be ignored and not added to any INDEX!!

At "Steps to Reproduce" You can only add one record with values (Null, Null) on unique index, when you add the second record you get a error. it means that NULL is treat as a value becouse add only one record with null!!

If the SQL Server tryed to follow SQL Standard it is a wrong implementation on SQL Server.

Any other SQL Database on the market treat NULL as NO VALUE and Add many record with NULL!!



create a filtered index to work around this should be 

Create Unique Index IxUniqueNotNull On dbo.TestIx(Cod, Val) Where Cod Is not Null And val Is not Null;

The functionality is implemented in Microsoft SQL Server, but the 'create' command do not follow the Standards. 

To join the SQL Standard, the option NULL' or 'NOT NULL' on Index Creation should come from the field definition in Table.
Sign in to post a comment.
Posted by Fabio Lunardon on 5/13/2011 at 12:03 AM
I agree with Jose 90%

A complex standard can not define everything.
But of course, consistency is more important than a standard
and each standard must be consistent.

On SQL language the concept of LEFT JOIN is defined,
and each missing value becomes NULL.
So NULL represents a missing value.
If it is missing, can not generate duplicates!

NULL values ​​are then added to the index,
but should not be taken into account
when evaluating a duplicate key.

Filtered index is a workaround,
but with many issues ...
Posted by Microsoft on 3/18/2011 at 2:02 PM
Hello Jose,

Thank you for submitting this suggestion, but we're trying to clean house and remove items we feel we will likely not address given their priority relative to other items in our queue. We believe it is unlikely that we will address this suggestion, and so we are closing it as “won’t fix”.

This cleaning will help us focus on the high-priority items that we feel need to get done, and we hope that it help provide better clarity to you about the issues we will (and won't) address.

Umachandar, SQL Programmability Team
Posted by Microsoft on 11/25/2009 at 4:04 PM
Hi Jose,

Thankyou for this report. I've also read the lengthy discussions on this topic at // and at //

There's a bunch of issues woven thru the discussion, which I'll try to tease apart:

a) Yes, SQL-Server differs from some other vendor products in this behavior. That doesn't make them right and SQL-Server wrong.(The "Might is Right" argument). But I do agree it makes migration among vendor products harder. However, dig deep and you'll find a host of subtle diffs between products (try delving into binding rules :-)

b) There's enough ambiguity about what NULL means, and its behavior, for us to argue forever. Nor is this solved by the ISO Standard, alas. Skim the 1400+ pages of ISO/IEC-9075:2008 and we find (in Part 1, Foundation):

" null value: A special value that is used to indicate the absence of any data value."

As a definition, it's certainly short, but doesn't pin down precisely how null should be treated in all situations.

c) The behavior you prefer is available, without too much pain, via Filtered Indexes.

d) Does SQL-Server behavior break ISO Standard? Well, strictly speaking, no - since this construct involves indexes, which are not part of the standard.

e) Despite d) above, does the behavior betray a deeper malaise where SQL-Server's treatment of nulls is non-conformant? Well, even that statement is subject to debate. Here is a quote from SQL:2008 (Part 1, again): Table constraints
A table constraint is an integrity constraint associated with a single base table.
... <snipped>
A unique constraint specifies one or more columns of the table as unique columns. A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.

Alas, this definition leaves us without guidance for the specific case we're discussing (null values in the unique columns).

Specific guidance may exist elsewhere in the ISO Standard. But even after a few hours of spelunking thru the official SQL:2008 specs, I can't find it. Please send me a reference if you track it down.

f) This behavior differs from ADO.NET. Yes, we can agree on this. Although .NET supports <nullable> types, it also chose bimodal logic, where TRUE and FALSE are the only options. This cuts back on the permutations, and complexity, of database' (TRUE,FALSE,UNKNOWN) behavior. Changing this behavior would require substantial work in the .NET Framework. Overall, I think database got it right :-)

Bottom line is that there is insufficient reason, even after all of the above discussion (plus the fact that the required behavior is available via Filtered Index), for us to change current behavior of SQL-Server in this situation.


Jim Hogg
Posted by José Guimarães on 11/25/2009 at 3:54 AM