Check Contraint VS Unique Index

  • Hello all i have a general question.

    We have a unique index on a table that is setup as a unique constraint. This gets thousands of writes and no reads. I was wondering what the difference would be if i removed this index and created a check constraint. Would the constraint act in the same way as the UNIQUE index where it would not allow duplicate values to be inserted into the table based on the column combination?

  • A check constraint is used to enforce limits on column values. For example CHECK Rate > 0 would ensure that only values greater than zero can be put into the Rate column. Check constraints have nothing to do with unique values. If you want to enforce uniqueness, you need a unique constraint or unique index

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the info. I never used the check constraints before so was wondering.

    As always your help is greatly appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply