SQL Server development group is repeatedly getting questions, suggestions and requests related to supporting unique indexes allowing multiple NULLs in SQL Server.
The SQL Standard requires that a column—or a set of columns—which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the DBMS implements an optional "NULLs allowed" feature. The optional feature adds some additional characteristics to the UNIQUE constraint:
The constraint is satisfied, if there are no two rows in (the relation) such that the value of each column in one row is non-null and is not distinct from the value of the corresponding column in the other row
SQL Server historically does support unique constraints both with the optional NOT NULL attribute for any participating column. If allowed, the NULL values are treated exactly as any other non-NULL value: if there are two identical keys with NULLs in the corresponding positions then they are considered duplicates violating the unique constraint.
In the following we will show how to implement unique constraint allowing multiple NULLs in SQL Server 2005. In fact you will learn much more because this technique can be easily generalized to allow not only multiple NULLs but also repetition of any other selected value in otherwise unique column. To keep the explanation simple we will consider single column constraint only.
Traditionally the proposed solution for this problem was using triggers. Users have to define update and insert triggers on the target table as it is described for example by Brian Moran in
http://www.windowsitpro.com/SQLServer/Article/ArticleID/25259/25259.html. We will show much more straightforward and better performing solution by using indexed views.
The idea is to create a view that filters out any values that we don’t want to violate unique constraint even if multiple rows for the same value are present. For example:
create table t (col1 int, col2 int)
create view v with schemabinding
select col1 from dbo.t
where col1 is not null
-- the following index will enforce uniquness of non-NULL values
create unique clustered index i on v (col1)
Allowing other values to be repeated is easy to accomplish as shown in the next example. Assume we have inherited table where previously application enforced uniqueness of column col1 for non-0, non-NULL and non -1 values and we would like to uphold this property going forward regardless of future ways of maintaining the table. To achieve this we should define our view as
where col1 is not null and col1<>0 and col1<>-1
Measurements on my laptop showed that the indexed view approach requires only approximately 50% of the CPU required by the trigger solution when inserting a batch of rows.
When you are considering the indexed view solution please make sure that the updates against the base table will work correctly. You must make certain that the following seven SET options are assigned correct values whenever an INSERT, UPDATE or DELETE is issued against the affected table. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING must be ON, and the SET option NUMERIC_ROUNDABORT must be OFF.
The outlined solution with indexed views works in SQL Server 2000 and SQL Server 2005 in Express, Workgroup, Standard, Enterprise and Developer Editions.
PingBack from http://www.hilpers.pl/48948-ms-sql-i-unique-nie
The years tick by, I noticed that sql 2008 has support for filtered indexes. While this does solve the issue. It would make sense to unify a bit more with what every other database does ie make null non matching in indexes.