Posted by Erland Sommarskog on 05/27/05 13:26

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Indexes do have one potential advantage. You can declare the
> IGNORE_DUP_KEY option on an index but not on a constraint. That's not
> much advantage in my view because there are few, if any, situations in
> which I think the IGNORE_DUP_KEY option is a good idea. In SQL Server
> 2005 constraints have the IGNORE_DUP_KEY option too!

I agree that IGNORE_DUP_KEY is not very useful.

However, there is another index option which is not available for
constraints which is more useful and that is DROP_EXISTING. If you for
some reason want to change a clustered index, dropping it and then
creating the new definition of the index, SQL Server has to rebuild
the non-clustered indexes twice. (Because the NC indexes uses the
clustered index key as the row locator.) DROP_EXISTING makes it possible
to do the change in one step. (I don't have the SQL 2005 docs handy, so
I can't say whether this is available for constraints in SQL 2005.)

My personal strategy is to use a constraint if it reflects some logical
property of the table, and index if it just happens to be unique. (The
typical reason that an index "happens" to be unique is when the primary
key is included as the last column or similar.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]