Single Predicate Check Constraint Gives Constant Scan but Two Predicate Constraint does not

Question

I can create the following constraint on the AdventureWorks table Person.Person:

ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')

This tells SQL Server that no LastName can have the value of Doesn't Exist

The Optimizer uses this to its advantage in the following simple query:

SELECT  *
FROM    Person.Person
WHERE   LastName = N'Doesn''t Exist'

As the constraint tells the optimizer that nothing in the column can equal the value we are equality searching for (assuming a trusted constraint), the optimizer just performs a constant scan and does "nothing"

If I drop the constraint above and create a slightly different one:

ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan2 CHECK (LastName <> N'Doesn''t Exist' AND FirstName <> N'Doesn''t Exist')

and run a query with a predicate who's results would violate the check constraint:

SELECT  *
FROM    Person.Person
WHERE   FirstName = N'Doesn''t Exist' AND
        LastName = N'Doesn''t Exist' 

We get an index seek with a Key Lookup

However, If I run

SELECT  *
FROM    Person.Person
WHERE   FirstName = N'Doesn''t Exist' AND
        LastName = N'Doesn''t Exist' 

with just the original constraint in place:

ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')

Again, I get the constant scan

Why can we not get a constant scan when running the query with two predicates when the constraint prohibits its results? Am I right in assuming this is just a limitation of the functionality within the optimizer?

Answer 1

Domain analysis (and any resulting simplification) only works with column-level check constraints.

Any check constraint referencing only a single column is a column-level constraint.

Any check constraint referencing multiple columns is a table-level constraint.

parent_column_id in sys.check_constraints is non-zero for a column-level constraint.

For clarity, it doesn't matter which syntax is used to declare or add the constraint. A check constraint declared at table level, but which references only a single column, will be considered a column-level constraint. Likewise, a check constraint referencing only a single column added after table creation will be added as a column-level constraint.

Yes, this is a 'limitation'. The logic is quite complicated enough when only column-level constraints need to be evaluated against complex query expressions. Expanding this to work for multi-column check constraints would be quite challenging.

That said, early simplifications are deliberately limited to common cases that are cheap and easy to apply. People used to optimizing compilers in programming languages often have different expectations for this sort of thing.