What's the difference between creating a UNIQUE index as "index" or as "constraint" in SQL Server?

Question

When creating an index over a column that is going to be UNIQUE (but not the primary key of the table), SQL server let's me choose a few options:

1) I can choose for it to be a Constraint or an Index.
I'm guessing this means that if I set it as constraint, it won't use it when querying, only when writing. However, the only efficient way I can think of for SQL Server to enforce that constraint is by actually building an index. What is the use for this option?

2) Also, if I set it as "index", it let's me specify that it should ignore duplicate keys. This is the most puzzling for me...
I again guess it means the opposite of constraint. It probably means "use it when querying, but don't even check when writing".
But then why would I set it as UNIQUE?
I'm guessing there are some optimizations SQL Server can do, but i'd like to understand it better.

Does anyone know what exactly SQL Server does with these options?
What's the use case for setting an index to be Unique, but ignore duplicate keys?

NOTE: This is for SQL Server 2000

---

EDIT: According to what you said, however... If I create a Constraint, will it be used to speed up queries that filter using the fields in the constraint?

Thanks!

Answer 1

SQL Server will build an index to implement UNIQUE constraints. You can see a reference to the unique index that is used to enforce unique constraints in the sys.key_constraints view (in 2005 — sorry, I don't know the 2000 equivalent). But both versions will use the index when querying.

The difference is that if you create an index you have more control over how it's built. In particular, you can include additional columns that may be looked up frequently along with the key.

Both options will allow you to "ignore duplicate keys" on existing data, but both will raise an error if you attempt to insert a new value that duplicates an existing one.

Answer 2

There is no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database.

Answer 3

A UNIQUE constraint is part of the ISO/ANSI SQL standard, whereas indexes are not because the Standard is implementation agnostic. SQL Server, in common with most SQL DBMSs, will use an index to implement a UNIQUE constraint.

Arguably, using UNIQUE rather than index in a SQL script is slightly more portable but as always the proprietary syntax should not be ruled out if it provids opportunities for optimization etc.