However, as you are working in a larger environment, the optimizer may even choose to use a non-covering index with less than fantastic selectivity just because the cost of the scan is so enormously high. In this case you could see a key lookup to pick up the uncovered columns.
...
Potentially instead of doing a DATEDIFF which includes both columns, you could hypothetically leave the one date column alone and do a comparison with a DATEADD on the other column, but since the other column isn't SARGable you'll still have it looking at each record. In a huge environment this will result in a crushing performance hit unless the result set is otherwise greatly reduced by some other criteria.
Isn't that somewhat self-contradictory? Isn't it better to have one of them SARGable than neither? Assuming you already have an index on that one column (if neither column is in any index, don't see how you avoid a scan no matter what you do). I would think the other column would have to be in the index as well, even if just INCLUDEd .... but the optimizer gets better all the time, so I won't try to limit it 🙂 .
I believe he wanted specific code of how to do the compare leaving one column alone, not just a generic statement.
Scott Pletcher, SQL Server MVP 2008-2010