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 (
NOT NULL PRIMARY KEY,
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.