MCTS 70-433 Unique & NULL "Gotcha"

This a bit of a gotcha - this makes sense but is not 100% intuitive? Apparently you can only have a single null value in a unique column.

 

Code Snippet

CREATE TABLE Numbers

(

       Number int primary key,

       Word  varchar(20) null UNIQUE

)

 

Go

Insert Into Numbers values(1,'One') -- Success

Insert Into Numbers values(2,'Two') -- Success

Insert Into Numbers values(3,'Two') -- Error is expected bcs Two is duplicated

Insert Into Numbers values(6,NULL) -- Success

Insert Into Numbers values(7,NULL) -- Suprise, the error for duplicate

 

So, In SQL Server if we add the UNIQUE constraint we can’t add more than one NULL values in the table.

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/a298b63b-e1eb-4b31-a2d7-64e1fe493b0a