Unique Index

This is another one of those small things that people run into when porting over their application from Oracle to SQL Server/DB2 LUW.  A unique index on a column in Oracle allows multiple NULL enteries.  That is not true though in the case of SQL Server and DB2 LUW.  In SQL Server and DB2 LUW, you can have only one single NULL value in the unique index and in the case of covered indexes (indexes on more than one column), you can have only one unique set and NULL is counted as a value for the purposes of a UNIQUE INDEX.

So, for an example…in SQL Server and in DB2 LUW:
create table test (col3 int, col4 int);
create unique index test1 on test (col3, col4);

1 null -> unique
2 null -> unique
2 null -> duplicate –> will give an error
null null -> unique
null null -> duplicate –> will give an error
null 1 -> unique.