SQL Server Home
Treat NULL as Standard SQL NULL instead 'unknown value'
as Won't Fix
11/25/2009 3:23:36 AM
User(s) can reproduce this bug
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.
SQL Server 2008 - Express Edition
Windows Vista 64-bit
Operating System Language
Steps to Reproduce
Create Table TestIx(
Id Integer Not Null,
Val Integer Null,
Primary Key (Id)
Create Unique Index IxUnique On TestIx(Cod, Val);
Insert Into TestIx( Id, Cod, Val ) Values ( 1, 1, 1);
Insert Into TestIx( Id, Cod, Val ) Values ( 2, Null, Null);
-- Until now SQL Server works fine!
Insert Into TestIx( Id, Cod, Val ) Values ( 3, Null, Null);
-- Now you can't insert again record with values (Null, Null) on unique index. it means that NULL is treat as a value becouse add only one record with null!!
In Portuguese the error message show (Message 2601 level 14):
Mensagem 2601, Nvel 14, Estado 1, Linha 1
Não possível inserir uma linha de chave duplicada no objeto 'dbo.TestIx' com índice exclusivo 'IxUnique'.
A instrução foi finalizada.
No error on Insert record with NULLs!
All other SQL Databases in the market you can insert many records without error!
to post a comment.
Please enter a comment.
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 ...
on 3/18/2011 at 2:02 PM
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
on 11/25/2009 at 4:04 PM
Thankyou for this report. I've also read the lengthy discussions on this topic at //connect.microsoft.com/SQLServer/feedback and at //social.msdn.microsoft/forums.
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):
"18.104.22.168 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):
22.214.171.124 Table constraints
A table constraint is an integrity constraint associated with a single base table.
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.
on 11/25/2009 at 3:54 AM
to post a workaround.
Please enter a workaround.
© 2014 Microsoft