How can I enforce uniqueness on a NVARCHAR(MAX) column in SQL Server?

Question

I have a column in SQL Server 2008 R2 that is NVARCHAR(MAX). I would like to set a unique index on that column, but the maximum size for a column that is included in a unique index is NVARCHAR(450) or 900 bytes.

How can I enforce uniqueness on a NVARCHAR(MAX) column?

Thanks!

Answer 1

1. Create a persisted column on some kind of hash, such as CHECKSUM. For example: Hash = MyStringHash as CHECKSUM(MyString)
2. Create a non-unique index on that column

3. Create a trigger that enforces uniqueness. The index is needed to speed up the search in EXISTS clause:

           WHERE   NOT EXISTS ( SELECT 1
                                FROM   YourTable AS y
                                WHERE  y.MyStringHash = CHECKSUM(Inserted.MyString)
                                       AND y.MyString = Inserted.MyString) ;
   

Important: you need to test with respect to your collation. If you are using a case-insensitive collation, make sure that the trigger will not allow both 'MyTest' and 'MYTEST'.

If you go for a unique index and stop at that, you are just creating a bug waiting to happen.

Edit: in a case-insensitive environment I have used CHECKSUM for a persisted computed column, which is fast, case-insensitive, and selective enough.