SQL Server gets an ANSI compliant unique index … sort of

If you work with any of the other big database platforms you’ve probably noticed that SQL Server’s implementation of a unique index is “different” than the others.  Until now there hasn’t been a way to fix that without using a trigger.  Until now…

But first, a little background information.

When you create a unique index in SQL Server (assuming a single column index here), you can have a single row with each value, and a single null value.

When you create a unique index in Oracle, or any other database which uses the ANSI compliant unique index (again assuming a single column index), you can hand a single row with each value, and as many null values as you want.

The logic of allowing multiple NULL values (which I agree with) is that NULL isn’t a value, its unknown, so who’s to say that two unknown values aren’t different.

With SQL Server if you wanted to enforce this you’d have to write some logic into a trigger to enforce this.

With SQL Server 2008 and up, you no longer need to do this.  A standard unique index can now be used to give you an ANSI complaint unique index.  The trick, is to use a filtered index.

When you create a unique filtered index the uniqueness is only applied to the values which are stored in the index.  Values which aren’t stored in the index aren’t required to be unique.  So if we create a unique index on a column where the column is not null, we have an ANSI complaint unique index.

We can test this with a little sample code.

[sql]create table test
(column1 int)
create unique index ux_test_c1 on dbo.test
where column1 is not null
insert into test
select null
insert into test
select null
insert into test
select 1
insert into test
select 2
select *
from test[/sql]



3 Responses

  1. Brianm
    No it can’t. Running the following will throw an error (shown in the SQL Comment). If you remove the WHERE clause from the CREATE UNIQUE INDEX then the constraint works (as expected).

    [CODE]create table t1
    (c1 int,
    c2 int)
    create unique index i1 on dbo.t1
    where c1 is not null
    create table t2
    (c1 int constraint fk_t2_t1 foreign key references t1(c1))

    Msg 1776, Level 16, State 0, Line 1
    There are no primary or candidate keys in the referenced table ‘t1’ that match the referencing column list in the foreign key ‘fk_t2_t1’.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    I like the idea of being able to use that as a candidate index. I’ve submitted this as a [A href=”https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=498009″]connect item [/A]. The more people that vote for it the better the chance that they will implement it in the next release (or the one after that).

  2. Thanx. I’m using 2005 here, and CREATEing a filtered INDEX failed. I appreciate you testing it and showing the results.

    I would see benefit in having a filtered INDEX as the target of an FK. I have run into such situations before.

    For example, let’s say i have a system that has products, and imports information from another system that uses their own ids on a subset of products.

    Product(MyId PK, Their_Id UNIQUE filter out NULLs);
    Product_Info_From Other_System(Their_Id, info….)

    I can modify the data on input to use our ids. However, if i want to keep the data as i get it, an FK to Their_Id would be nice. But, as they only have a subset of the products, i will have many NULLs in that COLUMN.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?