Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS NOT NULL' while the 'data' column of table t has no entries that are NULL and has indeed a NOT NULL contraint.

So I wondered whether the query optimizer would be able to ignore the condition or not. My thinking is that it cannot do that just because of the constraint (since it is not guaranteed if it was created with NOVALIDATE) but might be 'clever' enough to use statistics about the number of NULL fields in the column.

My own testing was inconclusive and I was unable to find any further informations on this topic.

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if:

• the column is declared as NOT NULL in the table definition, or

• the column is protected from nulls by an active, trusted check constraint

Here's a simple test table:

CREATE TABLE dbo.Test
(
    Id int IDENTITY(1,1) NOT NULL,
    DeclareNotNull int NOT NULL,
    DeclaredNull int NULL,

    CONSTRAINT PK_Test PRIMARY KEY (Id),
    CONSTRAINT CK_DeclaredNull CHECK (DeclaredNull IS NOT NULL)
);
GO

INSERT INTO dbo.Test
    (DeclareNotNull, DeclaredNull)
SELECT
    v.[number],
    v.[number]
FROM master.dbo.spt_values v
WHERE
    v.[number] IS NOT NULL;
GO

It has two columns: one is declared to be NOT NULL, the other is declared NULL but has a check constraint. Neither column has any rows with NULL values.

We can validate the check constraint like this:

SELECT 
    cs.[name],
    cs.[type_desc],
    cs.is_disabled,
    cs.is_not_trusted
FROM sys.check_constraints cs 
WHERE cs.parent_object_id = OBJECT_ID(N'dbo.Test');

We can then get estimated plans on these two queries:

SELECT * FROM dbo.Test WHERE DeclareNotNull IS NOT NULL;
SELECT * FROM dbo.Test WHERE DeclaredNull IS NOT NULL;

Notice there are no "Predicate" or "Seek Predicate" sections in the scan, or other filter operators in the execution plan. The null check has been removed in both cases.

If we disable the check constraint:

ALTER TABLE dbo.Test
NOCHECK CONSTRAINT CK_DeclaredNull;
GO

SELECT 
    cs.[name],
    cs.[type_desc],
    cs.is_disabled,
    cs.is_not_trusted
FROM sys.check_constraints cs 
WHERE cs.parent_object_id = OBJECT_ID(N'dbo.Test');

And we get the estimated plan for that second query:

The clustered index scan operator now includes a "Predicate" section, since the check constraint is not trusted.

StackExchange Database Administrators Q#274769, answer score: 17