Sometimes SQL Server gets it wrong, UNIQUE constraints and NULL is one of the parts where it is wrong. People call it a bug and request for it to be fixed, Microsoft calls requests to fix it common and offers workarounds and overall current SQL Server behavior does not adhere to the standard (ISO-9075).
To see the bug reports (and vote); 126533, 299229, 311223 (closed), 387273. Basically they are each others duplicates and from those, the request 299229 has most attention and the best discussion.
Available workarounds;
- Filtered index (starting with SQL Server 2008)
- Indexed view (starting with SQL Server 2000)
- Triggers (not recommended as the check is after the modification is completed and if invalid has to perform a rollback)
... and don't say Microsoft didn't get it right because it's Microsoft... Access implements UNIQUE constraints correctly.