WHERE NULL=NULL should never return true, so no rows returned is correct. It is only implementation defined in that some implementations get this wrong!¹³
SQL Server's handling of NULLs with in unique indexes/constraints is not standard. You can get better using a filtered unique index (“CREATE UNIQUE INDEX more_compliant_unique_index ON dbo.some_table(this_column) WHERE this_column IS NOT NULL;”), but be aware that filtered indexes have their own collection of gotchas⁴.
----
[1] MS Access IIRC, MS SQL Server if the ANSI_NULLS option set to off, MS² SSIS's default (consider NULLS equal) behaviour for merge steps, and so on.
[2] You may be noticing a pattern here… MS are not the only culprit though.
[3] I'm making a distinction here between places where behaviour is not defined in the standards, so implementations are free to go their own way, and where implementations go against what is expected by the standards.
[4] particularly with respect to the collection of unfixed/wontfix gnarly edge cases that is the MERGE statement.
If I CREATE TABLE foo (bar CHAR(1) PRIMARY KEY, baz char(1) UNIQUE), then different things happen on different databases.
In Microsoft SQL server, only one insert of a null into the baz column is allowed, and the null value is indexed.
In Oracle, null is never indexed in this context, so any number of null insertions into baz are allowed.
On a composite index, I believe that nulls are always indexed, on any database (they must be).
As far as I understand it, this is implementation-defined: