This week's digest is sponsored by two different concepts: "sargability" and "DDL triggers."
Here Simon Sabin (blog | twitter) talks about how the optimizer has a blind eye when using date-related functions against indexed columns – it should still be able to use an index when you have a filter or grouping on YEAR(indexed_datetime_column), for example.
Rob Farley (blog | twitter) raised a broader request to have the engine identify more cases where an index could be used – currently, the use of functions prohibits them, sight unseen. With a little more intelligence, the optimizer could squeeze a lot more performance out of some of these queries.
I presume chopswil wants these to show up more obviously in a query plan. I agree that it would be great if the default showplan XML came with reasons why a scan was chosen over a seek.
Greg Low (blog | twitter) entered this issue regarding a case that I've seen too, where an expensive DDL operation has been rolled back by Policy-Based Management – effectively doing the work twice instead of zero times.
This more generic request from Adam Machanic (blog | twitter) was referenced by Greg in the above item, but is marked as postponed, and has been categorized as such since 2006 (before SQL Server 2005 Service Pack 2 was released – the vehicle they said was too far along to include this feature). We've plenty of vehicles since then, and I hope they will add this feature soon.