SQL Server Performance

Difference between INDEX and CONSTRAINT

Discussion in 'T-SQL Performance Tuning for Developers' started by muko67, Sep 1, 2008.

  1. muko67 New Member

    Suppose I have a huge table with 10 Columns.
    I create a unique constraint on the columns (col1, col2 col3)
    If I use theese columns in a query WHERE col1=x and col2=y and col3=z
    The Question is whether the optimizer use these constraint in queries? Because I find some information in sysindexes table.
    And what is the difference if I create an unique index on the same columns(col1, col2, col3) ? Is it a duplicate or is there any difference?
  2. Adriaan New Member

    -- hm, why can't I delete this message? --
  3. satya Moderator

    [quote user="Adriaan"]
    -- hm, why can't I delete this message? --
    [:)] were there any comments you posted previously?
  4. Adriaan New Member

    You can have a unique index which is an index with an implied constraint, or a unique constraint for which an index (with a system-supplied name) will be created.
    Either way, there is an index, and the database engine will use this index when appropriate for the query.
  5. muko67 New Member

    Thanks a lot for the information.
  6. preethi Member

    The difference I faced is how to create and modify them. Index is an addition to the existing table which is created using CREATE INDEX statement. Constraint is created using ALTER TABLE statement. Additionally, Constraint name should be unique within the database where Index is unique within a table. In previous versions, you need to use special procedures to make schema adjustments where an index could be added easily.

Share This Page