Could any one pls. tell me the exact difference between the Constraint and Index created on the table (SQL 2000). As i dont find any difference other than the syntax.

Chandru

__________________
Thanks,
Chandra

The difference is really a semantic one. A constraint is used to enforce database integrity; an index is used to provide an alternative means of accessing a row. As implemented in SQL Server, there is no material difference, at least as far as a PRIMARY KEY or a UNIQUE constraint is concerned. Both are in fact implemented as an index, and the query optimizer will use them in its query plans just like an index.

The only difference I am aware of is that you use the DROP INDEX command to drop an index, whereas you must use ALTER TABLE to drop these constraints, which of course is merely a syntactical difference as you mention.

Of course, there are other kinds of constraints, such as CHECK constraints, which don't have anything to do with indexes.

Jeff Mason
[email protected]

Thanks for your prompt response.

Chandru