Large tuple uniqueness constraints in SQL Server

Article
02/04/2009

SQL Server 2008 limits unique constraints to 900 bytes of data per tuple. Here is a technique for enforcing uniqueness over larger tuple sizes. In many cases, the technique is more efficient than SQL Server’s unique constraints and can be used for performance critical cases.

Hash index based implementation

Essentially, the approach stores a hash of the columns in that need to be unique and uses this to minimize the number of rows that need to be checked when a row is inserted or updated. A trigger is used to enforce the constraint. A script with test code can be found attached.

As an example, suppose you want to add a unique constraint over Column1 and Column2 to the table:

create table [Schema].[Table]

(

[Id] int not null constraint [PK_Table] primary key,

[Column1] nvarchar(max) not null,

[Column2] nvarchar(max) null

);

Firstly add a column to store the hash and an index over the hash column:

create table [Schema].[Table]

(

[Id] int not null constraint [PK_Table] primary key,

[Column1] nvarchar(max) not null,

[Column2] nvarchar(max) null,

[Internal_Hash_Column1_Column2] as checksum([Column1], [Column2]) persisted not null

);

create index [Unique_Hash_Table_Column1_Column2] on [Schema].[Table] ([Internal_Hash_Column1_Column2]) include ([Column1], [Column2]);

If the columns are typically large then you may get better overall performance by removing the included columns from the index. Secondly, add a trigger to enforce the constraint:

create trigger [ConstraintInsertTrigger_Table] on [Schema].[Table]

after insert, update as

begin

if (update([Column1]) or update([Column2])) and

exists (select *

from [Schema].[Table] as T

inner join inserted as I on I.[Internal_Hash_Column1_Column2] = T. [Internal_Hash_Column1_Column2] and

I.[Column1] = T.[Column1] and

(I.[Column2] = T.[Column2] or (I.[Column2] is null and T.[Column2] is null))

group by I.[Internal_Hash_Column1_Column2], I.[Column1], I.[Column2]

having count(*) > 1)

begin

if @@trancount > 0

begin

rollback transaction;

end;

raiserror(N'Error: Values in the columns [Schema].[Table] ([Column1], [Column1]) must be unique.', 16, 0);

end;

Care is needed in handling nullable columns like Column2.

Below are some simple test cases. The insert statement: