Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?
My limited testing shows that the &quot;IS NOT NULL&quot; 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 &quot;Predicate&quot; or &quot;Seek Predicate&quot; 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 &quot;Predicate&quot; section, since the check constraint is not trusted.