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

    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’)

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

Next,  run the following query:



    WHERE Status = ‘X’


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

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.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |