Enforce Uniqueness Without the Unique Constraint

If you’ve worked as a DBA for a while, you might have come across the problem of hitting the 900-bytes limit for unique constraints. I’ve developed a way to work around this limitation.

To work around the 900-byte limit, you can create an additional computed column that gives the checksum value of the string column. Checksum is an int data type, which means you can create a unique index for the computed column. Listing 1 shows sample code for this solution, which works on SQL Server 2005 and SQL Server 2000. In this case, a computed checksum column is created for the msg_hdr string column.

If you run the code in Listing 2, which inserts three rows without any duplicates and a fourth row with a duplicate msg_hdr, you get the error message: Msg 2601, Level 14, State 1, Line 5. Cannot insert duplicate key row in object ‘dbo.messages’ with unique index ‘msg_hdr_uk’. The statement has been terminated. As this message shows, the unique index enforces the uniqueness and fails the offending INSERT statement.

—Aviv Zucker, Intel