Hugo Kornelis (12/27/2011)
EDIT: After looking at the question in yet a bit more detail, I realise that the ability to rewrite the ISNULL is completely irrelevant to the question. The index seek uses StartDate, not EndDate. I'm not saying that the optimizer will not rewrite an ISNULL in some cases, but it doesn't in this case! The seek includes only the StartDate in the Seek Predicate; the predicate on the EndDate is in the (non-seek) predicate of the operator.
So I decided to dig a bit deeper, and I found out that the explanation about the ISNULL is, in fact, incorrect. The optimizer will (at least in this case) NOT rewrite the ISNULL to make it SARGable.
Here's the code I used to test it - using the CREATE TABLE Jeff provided in the question, and the data generator he posted in the explanation.
CREATE INDEX ix1 ON dbo.Event(EndDate) INCLUDE (StartDate)
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @DesiredStartDate DATETIME,
@DesiredEndDate DATETIME,
@BeginningOfTime CHAR(8),
@EndOfTime CHAR(8);
SELECT @DesiredStartDate = '14 Jun 2012',
@DesiredEndDate = '16 Jun 2012',
@EndOfTime = '99991231';
SELECT EventID, StartDate, EndDate
FROM dbo.Event
WHERE (@DesiredEndDate <= ISNULL(EndDate,@EndOfTime))
OPTION (RECOMPILE);
I ran the last query with the actual execution plan turned on, and the result was an index SCAN instead of an index SEEK on index dbo.ix1.