SQL Server unique constraint problem

Question

How to create a unique constraint on a varchar(max) field in visual studio, visually.

the problem is when i try it:

manage indexes and keys > add > columns

I can only chose the bigint columns, but not any of the varchar(max) ones.

Do I maybe have to use check constraints?

If yes, what to put in the expression?

Thnx for the info

Answer 1

You cannot put a unique constraint on a VARCHAR(MAX) column (which could be up to 2 GB of text!!). You just simply cannot.

The unique constraint is enforced by a unique index in the background, and SQL Server has a 900 byte limit on index entries. You also cannot put a unique constraint on a VARCHAR(2000) field for that reason.

You'll need to find another way to achieve what you're trying to do. You could e.g. calculate the length and something like a checksum over your text and put a unique constraint on those length and checksum columns.