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)




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



Comments (4)

  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



    declare @result int

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

    return @result



    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);

  3. Julie says:

    How does this trigger enforce atomicity of the select + insert commands? Can another thread insert the same row between the select command and the insert command in this trigger?

    1. Joseph.Pilov says:

      Julie, since the trigger runs inside the insert transaction which spawned it in the first place, certain level of protection is ensured – IX lock on the page where that row is inserted at a minimum. However, there are two possibilities: at the time of the select, if a duplicate row is found, then a rollback will occur. If no row is found, but you want no row to appear after the select is run to ensure the select against the underlying table disallows no other inserts, you can consider SERIALIZABLE isolation level. So a valid question and not pretty in terms of concurrency.

Skip to main content