Are the two equivalent? Which one is preferable?
(Seeder question - if you have over 2000 reputation points here, please refrain from answering this question for a week or so)
asked
What is the difference between a Unique Index and a Unique Constraint?
Since I only have about 635 I can answer :-) My answer is : Effectively none.
They're the same, however to be more self-documenting and more intuitive I prefer Unique-Constraint mainly because (imho) UNIQUENESS really should be a constraint. Plus constraint automatically adds the unique index when created.
In principle a constraint is a logical feature of the database whereas an index is part of the physical implementation level. In practice, SQL Server compromises physical database independence and enforces uniqueness for unique indexes just as it does for constraints. SQL Server also automatically creates an index to support each constraint. So they achieve much the same end result.
I agree with bonskijr's comment that it's generally better to use constraints rather than unique indexes without constraints. Most developers and many software tools expect uniqueness to be enforced through a constraint and will look for constraints in order to identify keys and understand the data model.
