Recently, I had the opportunity to present table and index partitioning and a follow up question came up regarding partitioning a table with unique constraint/index. The Special Guidelines for Partitioned Indexes article describes the right approach

 

"Partitioning Unique Indexes

 

When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key.

 

Note: This restriction enables SQL Server to investigate only a single partition to make sure no duplicate of a new key value already exists in the table.
 

If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness"

 

This has to be a somewhat common scenario: what if you have a table with a unique constraint on a numeric field and a datetime field and you would like to partition you table based on date (say monthly partitions). Then you are forced to make your numeric column the partitioning key. But in most cases, this would present problems: how do you tie an ID for example to a non-unique date value? In other words, you are forced to make the choice between taking advantage of partitioning and enforcing uniqueness in your table - an unpleasant circumstance indeed. As you can see, the recommended approach is to remove the unique constraint on the table and use a DML trigger to enforce uniqueness. That way you still ensure uniqueness, but you get to use the DateTime field as the partitioning key.

Here is an example of a DML trigger that you can build on top of to accomplish this task:

 

drop table t1

create table t1(c1 int,c2 int)

--create a covering index on the column so searches can be fast

 

create nonclustered index nclidx on t1(c1)

go

 

 

drop trigger tr1

go

create trigger dbo.tr1 on t1 

instead of insert

as

declare @val int, @str varchar (3000)

 
select top 1 @val=A.c1 from inserted a inner join t1 b on a.c1=b.c1

select @str='The value '+convert(varchar,@val)+' you are trying to insert into column c1 already exists in table. Rolling back...'

 

if exists (select a.c1 from t1 a inner join inserted b on a.c1=b.c1)

 begin

  raiserror (@str, 16, 1)

  rollback transaction

  return

 end

else

 insert into t1 select * from inserted

go