I have a varchar(max) column where users will populate a pre-given key from another system. I want to enable that the users are forced to enter a unique key which means no duplicate values on that specific column. Should I change the collation or is there some sort of check constraint that I can add?
Tell me more
×
Database Administrators Stack Exchange is a question and answer site for
database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
|
|
||||
|
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. |
|||||||||
|
nvarchar(max)is the correct data type for this column. Using the correct type ought to allow a simple unique constraint to be used. That said, the question could be improved by stating the problem you are looking to solve, rather than asking about implementation of the current 'solution'. – Paul White Feb 25 at 2:38