SQL does not consider trusted constraints ??
— Create a simple table, with a Check constraint on Column2
CREATE TABLE dbo.Test
(Column1 int NOT NULL, Column2 int NOT NULL, CONSTRAINT CK_Test CHECK (Column2 > 0) );
— I insert 5000 rows
INSERT INTO dbo.Test (Column1, Column2) values (1,2) go 5000
–the constraint is trusted, so the Optimizer should consider it:
SELECT * FROM sys.check_constraints;
–No, in the following query the optimizer does not consider it: it goes for Table scan! (5000 rows read!) Verify the execution plan!
— I expected to find a constant scan, the engine should not touch the table because the
— the WHERE predicate is not satisfied by the constraint.
SELECT Column1, Column2 FROM dbo.Test WHERE Column2 < 0
–But if I add another predicate 1=1, the Check constraint is considered, and the scan does not take place
SELECT Column1, Column2 FROM dbo.Test WHERE Column2 < 0 AND 1=1
The question is: WHY ? 🙂
Thanks very much for any suggestion
When you include something like 1=1, the Query Optimizer gets a little thrown in the parameterisation phase and decides not to do it. You can achieve the same with OPTION (RECOMIPLE). At that point, the zero becomes a non-parameterised value, and you get your contradiction.
So it's not that the Check is being ignored – not at all. It's just that parameterisation is trying to come up with a plan that can be used in more cases than just this one.