One of the benefits and uses of Filtered Indexes in SQL Server is to produce filtered uniqueness. clearly this has some implications, thus please perceive what you are making an attempt to accomplish. In the below example, we've a compound natural key of the 2 fields key1 and key2. the field bit1 isn't a member of the natural key, however will inform us as to some table usage. maybe bit1 indicates that this record is Active (1) or Inactive (0), or whether or not the info is Confirmed (1) or unconfirmed  (0), or Deleted (0) or Not Deleted (1).

In any case, we have a tendency to solely wish to enforce uniqueness for when bit1 = 1, that indicates:
This value is often filtered to be used wherever bit1 = 1
We do not care whether or not there are duplicate records for once bit1 = 0.
In this means, you'll "deactivate" (in business terms) a record by setting bit1 = 0, while not 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

And here is the output:

Note that rows four and seven 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 unsuccessful.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.