Add Check Constraint On Varchar Column to enforce no duplicates
Ordinarily, you'd just create a unique constraint on that column. But you can't put a unique constraint on a varchar(max) column. That could be close to 2GB per index entry.

In SQL Server, the maximum size of an index key is 900 bytes. I believe SQL Server enforces unique constraints by using a unique index, so I'd expect that limitation to apply to your problem, too.

In any case, I think this is an XY problem.