SQL Server gets an ANSI compliant unique index … sort of

Published On: 2009-10-15By:

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.

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


Contact the Author | Contact DCAC

3 responses to “SQL Server gets an ANSI compliant unique index … sort of”

  1. BrianTkatch says:

    Can a filtered UNIQUE INDEX be FKed to?

  2. mrdenny says:

    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).

  3. BrianTkatch says:

    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.


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link