Several people asked this. The answer is that the SARGability of the ISNULL is irrelevant, because the engine will use the first column in the index (StartDate) for the seek, not the second column (EndDate).

In my previous post, I have demonstrated that -at least in this case, with a 1000-row test population-, the optimizer will in fact NOT rewrite the ISNULL to a more SARGable expression. But what I think Jeff had in mind (and he'll undoubtedly correct me if I'm wrong) is that the ISNULL expression could be rewritten as follows:

Step 1: Convert @DesiredEndDate <= ISNULL (EndDate, @EndOfTime) to

@DesiredEndDate <= EndDate

OR (EndDate IS NULL AND @DesiredEndDate <= CAST(@EndOfTime AS datetime))

Step 2: Pre-evaluate the part of the expression that contains only variables:

@DesiredEndDate <= EndDate

OR (EndDate IS NULL AND (True))

Step 3: Remove needless ballast and swap order for readability:

EndDate >= @DesiredEndDate

OR EndDate IS NULL

In an RDBMS that sorts NULL values *after* other values, this can be retrieved using a single seek (for the first qualifying row) + forward range scan (for the rest). But SQL Server orders NULL values first. That makes it a bit harder; in theory, the optimizer could use two seek / forward range scan combinations (one for the NULL values at the start; the other for the non-NULL values) and merge them. Maybe this will indeed happen if the table is large enough to warrant using all phases of the optimizer to do a full optimization. But for a thousand rows, the optimizer will simply scan the index instead.