Dealing with Unique Columns when Using Table Partitioning


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

 

Comments (2)

  1. Bob Hovious says:

    Alternatively you could define a scalar function to check for the existence of a row with the value to be kept unique and then use that function in a check constraint.     Assume a partititioned table has an identity column named RowID, which is to be kept unique ACROSS partitions.    As you have pointed out,  the partitioning column must be included in the UNIQUE CONSTRAINT index,  which only makes RowID non-unique.  

    But by creating a function to check for preexisting rows:

    CREATE FUNCTION UniqueRowID(@RowID int)

    returns int

    as

    begin

    declare @result int

    select @result =  COUNT(*) from SomeTable where RowID = @RowID

    return @result

    end

    GO

    Then adding a CHECK constraint to the table:

    alter table dbo.uniqueness

    add Constraint TrulyUnique CHECK (dbo.UniqueRowID(Rowid) = 1);

    Attempts to insert duplicate rowIDs will conflict with the CHECK constraint "TrulyUnique" without any need for triggers.

  2. Alex C says:

    The add constraint line, should probably be:

    add Constraint TrulyUnique CHECK (dbo.UniqueRowID(Rowid) = 0);