Partilhar via

Achieving Selective Uniqueness in SQL Server Tables

  • Artigo

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:

  • Columns involved in a UNIQUE constraint may also have NOT NULL constraints, but they do not have to.
  • If columns with UNIQUE constraints do not also have NOT NULL constraints, then the columns may contain any number of NULL-'values'. (Logical consequence of the fact that NULL<>NULL.)

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

https://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)

go

create view v with schemabinding

as

select col1 from dbo.t

where col1 is not null

go

-- the following index will enforce uniquness of non-NULL values

create unique clustered index i on v (col1)

go

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

 

create view v with schemabinding

as

select col1 from dbo.t

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.

Comments

  • Anonymous
    January 11, 2006
    The comment has been removed

  • Anonymous
    January 12, 2006
    Please make sure your database is in 90 compatibility mode (database -> properties -> options).
    I have tried it and it worked for me in 90 compatibility mode but 80 complains about the ARITHABORT setting). I have run the following first to create and populate the table
    drop view v
    drop table t
    create table t (col1 int, col2 int)
    insert into t values (1,1)
    go
    create view v with schemabinding
    as
    select col1 from dbo.t
    where col1 is not null
    go
    -- the following index will enforce uniquness of non-NULL values
    create unique clustered index i on v (col1)
    go

    Then I have opened the table (right-click on the table name in the Object Browser) and changed the value in both columns to 2 and then hit the asterisk at the last row to perform the change.

  • Anonymous
    January 13, 2006
    Well..the database is actually on a SQL server 2000 machine, so I can't change the compatability mode setting. The modifications are fine within the old enterprise manager open table, but they fail in sql management studio go figure....

  • Anonymous
    January 13, 2006
    Jamin, thanks for the additional information. It turns out that in the Management Studio for “open object” we are using the SET options from the database itself, not the ones in the OPTIONS in the Management Studio. That implies a workaround – use sp_dboption to set the ARITHABORT in the database and then you can “open object” and update the table participating in the indexed view.

  • Anonymous
    January 14, 2006
    You've just described how SQL Server violates the SQL standard - it doesn't allow multiple rows with NULL in a column having a UNIQUE constraint. Rather than posting various kinds of workarounds, why not just fix it to adhere to the standard. This is absolutely killing me right now, and none of the workarounds work for me (existing data model and application which can't be gutted to handle this)!

  • Anonymous
    January 15, 2006
    I agree that SQL Server violates the optional "NULL allowed" feature as described in the SQL Standard. On the other hand, SQL Server's implementation was introduced before the SQL Standard came up with the “NULL allowed” exception.
    There is significant difference between "fixing" it in the product and showing a workaround in that the "fixing" will have to wait for the next release and I hope the workaround will help at least some of the users in the meanwhile. The implementation may seem trivial on the outside, but inside we will have to treat specially all unique indexes that allow a NULL because suddenly knowing the key value is not enough to identify the row in the table unambiguously.
    We are considering allowing multiple NULLs in unique keys in the next release but the plans are not firm yet so I cannot promise it will make it there. In this context I would like to ask if adding a new keyword requesting allowing multiple NULLs into the CREATE INDEX and CREATE CONSTRAINT statements will be an acceptable solution.

  • Anonymous
    January 24, 2006
    Hello Lubork,

    I believe that a keyword would be an excellent solution solving both needs (one null or multiple nulls) with the same type of constraint. Any chance of that making it into a service pack?

  • Anonymous
    January 30, 2006
    Well I am facing this and its pointless to have a UNIQUE contraint which does not allow multiple NULLs.
    To be honest <NULL> string can be inserted in a PK constraint rather and have PK (varchar) as a UNIQUE Constraint instead.

    We must have ability to have multiple NULLs allowed.


  • Anonymous
    March 02, 2006
    Expanding this issue a little, and providing for more control of individual constraint key elements,
    I have a problem where I would like to be able to add an ...

    EXCEPTION_ALLOW where [KeyElement] = value

    ... type of control to a CONSTRAINT to allow duplicate records on an exception case for a specific constraint key value.

    Comments?

  • Anonymous
    March 09, 2006
    Awesome. This is just the solution I was looking for. I have a couple applications that have Effective and Ending Dates. I have been looking for a way to make a constraint only when the EndDate is null (meaning it is the most current record). Now I finally have a solution. Thanks!

  • Anonymous
    May 02, 2006
    Why not just use a stored procedure that does the dupe checking as well as the insert?

  • Anonymous
    August 07, 2006
    There is nothing to loose from changing this behaviour to allow multiple NULLs on a column with a uniqueness constraint set (where the column is also flagged to Allow Nulls).

    There is nothing to gain from allowing a single NULL in a unique constrained column (the current behaviour!?). I would love to hear any instance where this is desirable.

    Why can't this behaviour just be changed to allow the standard behaviour? A column either allows or disallows NULLs. Regardless of whether there is a uniqueness constraint set.

    At the very least a global option should be available at the database level to allow for multiple nulls on unique constrained columns to be switched on or off ( perhaps in the same place as the ANSI NULLs and ANSI Default flags?)

  • Anonymous
    August 09, 2006
    Answer to Michael Lato:
    We are consering several options how to enable the multi-NULL uniqueness in the future releases. However, SQL Server is usually not introducing new features in the service packs so you should wait at least for the next release. Unfortunately the existence of the workaround outlined in this blog lowers the priority of this feature because we are addressing those w/out workarounds first.

  • Anonymous
    January 22, 2009
    PingBack from http://www.hilpers.pl/48948-ms-sql-i-unique-nie

  • Anonymous
    April 08, 2009
    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.

  • Anonymous
    June 15, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=24294

  • Anonymous
    September 28, 2010
    One of The Simplest way to achieve this using SQL 2008 is: CREATE UNIQUE NONCLUSTERED INDEX <IndexName> ON dbo.TableName(ColumnName) WHERE ColumnName IS NOT NULL; GO