This may be a stupid question, but I'm very curious:

What exactly is the distinction between creating a unique non-clustered index on a table, and creating a unique constraint on a table (which creates a unique non-clustered index behind the scenes)?

It seems to be exactly the same thing, but to require different commands to create and drop the resulting indexes. In the former case, you just DROP INDEX and CREATE INDEX, while in the latter, you have to ALTER TABLE DROP CONSTRAINT and ALTER TABLE ADD CONSTRAINT... and it's really hard to tell which is which using the tools or APIs. You have to REALLY want to know.


Is there any functional distinction between them? Is there a set of guidelines over when to use one over the other?



MS Sql Server LazyDBA home page