Will Check Constraints Improve Database Performance?

The short answer is yes, but only in some instances.

 Let’s say we have a table like following.

 CREATE TABLE CheckConst (

      ID Integer NOT NULL PRIMARY KEY,

    Status CHAR(1))

We will insert few records just for demo purposes.

INSERT INTO CheckConst (ID,Status) VALUES (1,’Y’)

INSERT INTO CheckConst (ID,Status) VALUES (2,’N’)

INSERT INTO CheckConst (ID,Status) VALUES (3,’Y’)

INSERT INTO CheckConst (ID,Status) VALUES (4,’Y’)

Next,  run the following query:

SELECT *

    FROM CheckConst

    WHERE Status = ‘X’

            OPTION(RECOMPILE)

Obviously, you will receive zero rows as there are no records for Status = ‘X’, However the query engine knows this and performs either an index scan or a table scan.

 

If you check the IO and CPU cost you will see (in the following image) there is some cost associated with this operation.

Then we will add a constraint to status column as following.

ALTER TABLE CheckConst ADD CONSTRAINT chk_status CHECK(Status = ‘Y’

        OR Status = ‘N’)

Now run the same query again:

Now the query engine does not perform either a table scan or an index scan because it knows that there are no matching records from that table for the given the criteria.

If you check the cost now it is almost zero.

Therfore, by adding a constraint there is a performance gain when querying data that does not match a constraint.

]]>

Leave a comment

Your email address will not be published.