The answer is, It is because that NULL values cannot be eaqual to each other.

That means that server is not able to distinguish these values so it treats them as one value.

It was already raised as suggestion: http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values#

NULL, to me, is undefined. Whenever a value is undefined, it cannot be classified as unique, or cannot be used in a comparison opeartion.

To go back to the electronics world, we have ON (1), OFF (0) and a third "tri-state", which can be used to understand NULL or "undefined". Because undefined cannot be compared, we only have 1 NULL allowed.

Though ANSI standard SQL92 states that a UNIQUE constraint should allow multiple NULL values, SQL Server implements its own way of UNIQUE constraints and disallow multiple NULLs in unique constraints. First I faced with such problem during database migration from MySQL to SQL Server.

NULL value is also treated as an unique value like 1,2,3..... Since it has an unique constraint, it wont allow a duplicate value another NULL value.

This is due the Three-Valued Logic behavior of the SQL Server.

UNIQUE constraints, set operators (such as UNION and EXCEPT), and sorting or grouping operations, NULLs are treated as equal in SQL Server. Hence we can have only one NULL value on UNIQUE constraint.

The definition of Unique key says that only Unique values are allows. Means if we have 2 null values, both will be same and violate the definition.

Its all possible right now!!

CREATE TABLE TestUniqueIndex(Id int identity(1,1) PRIMARY KEY, Name varchar(20) NULL);

GO

CREATE UNIQUE INDEX IXTestUniqueIndexName ON TestUniqueIndex(Name) WHERE Name IS NOT NULL;

GO

INSERT INTO TestUniqueIndex SELECT 'Bill';

INSERT INTO TestUniqueIndex SELECT 'Bob';

INSERT INTO TestUniqueIndex SELECT 'Barry';

INSERT INTO TestUniqueIndex SELECT 'Boris';

INSERT INTO TestUniqueIndex SELECT null;

INSERT INTO TestUniqueIndex SELECT null;

INSERT INTO TestUniqueIndex SELECT 'Bill'; --fails

You can get around this limitation by using a unique index with a WHERE IS NOT NULL clause.

My view is: Eventhough NULL is not equal to NULL, The NULL value's nature is UNKNOWN, So to enforce uniqueness KNOWN values allowed only once, likewise only one UNKNOWN values is allowed

It was a design decision made years ago by Sybase (SQL Server on Windows was originally written by Sybase and licensed by Microsoft). In order to maintain backwards compatability, the decision has been carried forward.

The reason behind not allowing Multiple NULLs is becuase of the Unique Index that is created when a unique constraint is created. Even though NULL in not considered equal to NULL, but when Unique Index is created it treats two NULL values as equal and that is the reason for not allowing multiple NULLs when you have unique constraint on a column. To change this behavior MS might have to change a lot of code and hence the reason for keeping it same til yet.

declare @t table(i int unique) insert into @t select 1 union all select null union all select null

sql treats nulls as same, replace union all with union and see what happen.

or check the difference by adding below select * from @t select distinct * from @t