SQL Server with Mr. Denny

Oct 15 2009   11:00AM GMT

SQL Server gets an ANSI compliant unique index … sort of

Denny Cherry Denny Cherry Profile: Denny Cherry

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)
go
create unique index ux_test_c1 on dbo.test
(column1)
where column1 is not null
go
insert into test
select null
go
insert into test
select null
go
insert into test
select 1
go
insert into test
select 2
go
select *
from test

Denny

3  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BrianTkatch
    Can a filtered UNIQUE INDEX be FKed to?
    0 pointsBadges:
    report
  • Denny Cherry
    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) go create unique index i1 on dbo.t1 (c1) where c1 is not null go create table t2 (c1 int constraint fk_t2_t1 foreign key references t1(c1)) go /* 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. */[/CODE] 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).
    66,070 pointsBadges:
    report
  • BrianTkatch
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: