Dealing with Unique Columns when Using Table Partitioning

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

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)

 

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.