Making varchar(500) datacolumn unique

I have a requirement to make a column of varcha开发者_开发知识库r(500) unique.

Putting unique constraint would not work as it crosses the size limit of unique.

Then, what would be the best strategy to make column unique?

Create another field with the HASH of that varchar(500), and put a UNIQUE CONSTRAINT on the hash field: HASHBYTES('md5', MyLongVarcharField)

This will cause poor performance but if you have a varchar(500) where you need to enforce uniqueness I'm assuming performance isn't on the forefront of your considerations anyways.

EDIT:

To clarify, the chance of having two strings output the same 128 bit hash value is 1 in 340,282,366,920,938,000,000,000,000,000,000,000,000. It's unlikely but not categorically impossible that you could have a collision.

If you are still concerned you can use SHA or SHA1 algorithms which are 160 bits.

You can use a DML trigger on INSERT and UPDATE. This gets around having to use a hash. I chose an AFTER trigger for simplicity, but this can be easily done with an INSTEAD OF trigger as well.

create trigger dml_EnforceUniqueVal
on dbo.UniqueBigColumn
after insert, update
as  
    declare @CountOfRepeats int
    select @CountOfRepeats = COUNT(*)
    from UniqueBigColumn
    where somestring in
    (
        select somestring
        from inserted
    )

    if @CountOfRepeats > 1
        rollback
go