Not Too Unique

My personal favorite when it comes to databases is Microsoft SQL Server. However, reality dictates that I sometime have to use different database. Actually quite often I need to have one application supporting different database servers. I might need application to work on SQL Server, PostgreSQL and (god forbid) Oracle.

With time I got used to prefer standardized parts of SQL. It makes life a lot simpler and SQL Server is usually ok with that plan. Except when it comes to NULLs where things can get little bit quirky. Setting ANSI_NULLS to true does sort most of issues but one.

If you have unique index on nullable column, SQL compliant behavior is to force uniqueness on all values except null ones. That means that having "a", "b", null, null is valid scenario. In SQL Server that second null will not be allowed under premise that such value already exists and thus violates unique index. That “feature” is part of SQL Server since it’s very first days and it is very unlikely that it will be changed in the future. Compatibility is a bitch sometime.

Fortunately there is a workaround since SQL Server 2008 in form of filtered indexes. That enables unique index on only some values. In our scenario we just need to ignore nulls and standard behavior here we come:

CREATE UNIQUE INDEX UX_MyIndex ON MyTable(MyColumn) WHERE MyColumn IS NOT NULL;