SQL Server's Partial Index

" ...So we should all use PostgreSQL, because it teaches us how to use the newer versions of SQL Server before they come out. :) " – http://www.postgresonline.com/journal/archives/195-Conditional-Uniqueness-with-Partial-Indexes.html




SQL Server 2008 already has partial index, which they termed as filtered index.

One possible use of partial index is to make conditional uniqueness on some rows.



Prior to version 2008, SQL Server don't have a mechanism to create uniqueness partially. Its uniqueness always applies to all the rows in the table. This limitation sticks out like a sore thumb, as there are certain cases when we don't want to apply uniqueness on certain rows, e.g. soft delete mechanism comes to mind.

create table ThePerson
(
    PersonId int identity primary key,
    PersonName nvarchar(100) not null,
    EmailAddress nvarchar(100) not null,
    IsDeleted bit not null default 0,
);
 
create unique index ux_ThePerson
on ThePerson(EmailAddress);
 
 
insert into ThePerson(PersonName, EmailAddress) values
('John Lennon','j.lennon@england.com'),
('Paul McCartney','p.mccartney@england.com');
 
 
update ThePerson set IsDeleted = 1 where PersonName = 'John Lennon';

 
-- this will have an error and won't be inserted to the table, as the unique constraint is applied to all rows, which includes the soft-deleted John Lennon row:
insert into ThePerson(PersonName, EmailAddress) values('Julian Lennon','j.lennon@england.com')

If we applied unique on e-mail address, when we soft delete the record John Lennon, the system should allow us to introduce another person with an e-mail address of j.lennon@england.com

However, the applied ux_ThePerson index will prevent us on adding another record with same e-mail address. We can add another person with same e-mail address if we hard delete John Lennon; or if we want to maintain soft delete mechanism, we just need to remove the unique index on EmailAddress, but the effect is not something to be desired of, not enforcing business rules(e.g. unique) on the database level could allow bad data to slip in to our records, e.g. some persons can have the same e-mail address. Not enforcing unique constraints on your entities, denies your system of being able to fail fast when invalid data happens to slip in



Enter partial index. This technology allows us to enforce unique constraints partially, i.e. not on all rows. SQL Server 2008 has this technology already, which is called filtered index



To allow Julian Lennon to be added to the database, enforce unique constraint on non-deleted rows only, to wit:

create table ThePerson
(
    PersonId int identity primary key,
    PersonName nvarchar(100) not null,
    EmailAddress nvarchar(100) not null,
    IsDeleted bit not null default 0,
);
 
 
create unique index ux_ThePerson
on ThePerson(EmailAddress)
where IsDeleted = 0;

 
 insert into ThePerson(PersonName, EmailAddress) values
('John Lennon','j.lennon@england.com'),
('Paul McCartney','p.mccartney@england.com')
 
 
-- This will have an error: Cannot insert duplicate key row in object 'dbo.ThePerson' with unique index 'ux_ThePerson'.
-- insert into ThePerson(PersonName, EmailAddress) values('Julian Lennon','j.lennon@england.com')
 
 
update ThePerson set IsDeleted = 1 where PersonName = 'John Lennon';
 
-- Upon soft-deleting John Lennon, other Person can assume the e-mail j.lennon@england.com
insert into ThePerson(PersonName, EmailAddress) values('Julian Lennon','j.lennon@england.com');

On the line 12 of the above DDL, the enforcement of unique constraint is done on non-soft-deleted rows only. Having applied the unique constraint partially, after soft-deleting John Lennon, we can now insert Julian Lennon that assumes the e-mail j.lennon@england.com



Happy Coding! ツ