Using a Filtered Index to Enforce Filtered Uniqueness

One of the advantages and uses of Filtered Indexes (introduced with SQL Server 2008) is to provide filtered uniqueness. Obviously this has some implications, so please understand what you're trying to accomplish.

In the below example, we have a compound natural key of the two fields key1 and key2.  The field bit1 is not a member of the natural key, but does inform us as to some table usage. Perhaps bit1 indicates that this record is Active (1) or Inactive (0), or whether the data is Confirmed (1) or Unconfirmed (0), or Deleted (0) or Not Deleted (1).

In any case, we only want to enforce uniqueness for when bit1 = 1, which indicates:

1. This value is regularly filtered for use where bit1 = 1
2. We don't care whether there are duplicate records for when bit1 = 0.

In this way, you could "deactivate" (in business terms) a record by setting bit1 = 0, without violating your natural key's uniqueness on (key1, key2).

drop table dbo.tabletestdups 
go
create table dbo.tabletestdups 
( key1 int not null
, key2 int not null
, bit1 bit not null
)
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1) 
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) --succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,1) --succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,1) --succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,0) --succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,0) --succeed
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) --fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --fails
go
drop index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups 
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1) WHERE bit1 = 1 --Note the important WHERE clause here at the end of the index.
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) --fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --succeeds because the unique constraint only enforces bit1 = 1.
go
select * from dbo.tabletestdups

Note that rows 4 and 7 have allowed duplicate combination of key1 =1, key2= 1 and bit1 = 0, but that previous attempts to insert a duplicate combination of key1 =1, key2= 1 and bit1 = 1 failed.

Filtered indexes also provide some powerful tuning and storage advantages for commonly-accessed search predicates, with some caveats. Here is an excellent article explaining the caveats: http://blogs.msdn.com/b/timchapman/archive/2012/08/27/the-drawback-of-using-filtered-indexes.aspx