Run the following code
declare @t table(i int unique)
insert into @t
select 1 union all
select null union all
You will get the following error
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__#A989971__3BD019979D26A3BB'. Cannot insert duplicate key in object 'dbo.@t'. The duplicate key value is ().
The statement has been terminated.
But other RDBMSs like ORACLE, MySQL, etc will allow multiple NULL values on a column defined as Unique key. It is because that NULL values cannot be equal to each other
Why does SQL Server allow only one NULL value on a column that has unique constraint?
Tags: t-sql, sql_server, sqlserver, tsql, question,