First published on MSDN on Apr 02, 2013

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)








drop trigger tr1




create trigger dbo.tr1 on t1


instead of insert




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)




raiserror (@str, 16, 1)


rollback transaction








insert into t1 select * from inserted