Hmm, just to come back on this. I tested both solutions after running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE each time (I'm using a test instance obviously!) and i got the following stats:

My solution:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 24 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(8192 row(s) affected)

Table '#DateTest________000000000003'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 223 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Jeff's solution:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 11 ms, elapsed time = 11 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(8192 row(s) affected)

Table '#DateTest________000000000003'. Scan count 2, logical reads 72, physical reads 0, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 261 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

So in fact my solution is marginally faster and involves less i/o, but this is for a relatively small table. There will come a point, however, at which the table/index scan will become more expensive than the index seek and the other operators in the plan produced by Jeff's solution.

Regards

Lempster