CHECK an optimized query plan

Question

I'm suprised by the behaviour of SQL Server 2008(r2).

I've defined a table with a column like this:

ex int not null default 1 check (ex = 1)

And I expected queries like

select *
from t20130603_b
where ex = 0

to have query plans where there's no rows fetched, just a constant value used (since ex = 0 is impossible).
But there are (full scan).

Did I get/do something wrong?

Answer 1

A couple of possibilities. The plan may have been auto parameterized or the check constraint may not be trusted.

CREATE TABLE t20130603_b
  (
     ex INT NOT NULL DEFAULT 1 CONSTRAINT ck CHECK (ex = 1)
  )

/*Plan shows table scan*/
SELECT *
FROM   t20130603_b
WHERE  ex = 0 

In SQL Server 2012 the text in the graphical plan shows that auto parameterization was attempted (the text is ex = @1) on previous versions you may need to look at the XML version of the plan. Because the literal 0 was replaced with a parameter @1 before compilation the contradiction with the check constraint cannot be detected.

More details about auto parameterization are here. That article mentions that queries with IN Expressions don't get auto parameterized. Trying the below

SELECT *
FROM   t20130603_b
WHERE  ex IN ( 0, 0 )

Gives the following plan

The table is no longer accessed and is replaced with a constant scan. To see the issue with untrusted constraints you can try.

/*Disable constraint*/
ALTER TABLE t20130603_b
  NOCHECK CONSTRAINT ck

/*Re-enable without checking existing data. 
Constraint is not trusted*/
ALTER TABLE t20130603_b
  CHECK CONSTRAINT ck

SELECT is_not_trusted
FROM   sys.check_constraints
WHERE  name = 'ck'

SELECT *
FROM   t20130603_b
WHERE  ex IN ( 0, 0 )

The above goes back to the original table scan plan.

/*Correct way of re-enabling constraint*/
ALTER TABLE t20130603_b
  WITH CHECK CHECK CONSTRAINT ck

/*Constraint is trusted*/
SELECT is_not_trusted
FROM   sys.check_constraints
WHERE  name = 'ck'

/*Back to constant scan*/
SELECT *
FROM   t20130603_b
WHERE  ex IN ( 0, 0 )

After the constraint is trusted again it is back to the constant scan.