I did some tests on the use of IsNull in the Where clause and found the following:

If the column being tested allows nulls, it will use an index or table scan (depending on whether there's an index available, of course), even if there are no nulls in the column. If the column does not allow nulls, SQL server ignores the isnull function and uses an index seek.

This made me wonder if a check constraint on a column would have similar behavior.

create table dbo.CKTest (

Date datetime primary key)

go

insert into dbo.CKTest (date)

select distinct date

from common.dbo.dates

where datepart(year, date) = 2007

Then I ran:

select date

from dbo.CKTest

where datepart(year, date) = 2007

Got a clustered index scan (of course).

Then added:

alter table dbo.CKTest

add constraint CK_Date_2007 check(datepart(year, date) = 2007)

Ran the same select. Still a clustered index scan. Not a seek.

So there's a slightly different mechanism in place for making SQL ignore IsNull vs DatePart, even when it could know before-hand what the result is going to be.

Good to know on the IsNull thing. Would never have thought of checking that, since I'd never run IsNull on a non-nullable column. Not sure what practical use it is, but definitely interesting to know.