SQL does not consider trusted constraints ??

Alessandro 2017-01-13 21:32:12

Hello community, I am dealing with a strange problem: summaryzing:

— Create a simple table, with a Check constraint on Column2


  (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

Rob Farley 2017-01-16 00:17:27
This is about parameterisation. In the first query, notice the Predicate on the Scan – it uses @1. It figures that you want a plan that can be reused no matter what threshold you pass in. It might be 0 today, but tomorrow it might be 7. The only reusable option is to scan the table.

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.