Connect Digest : 2011-04-03

This week's digest is sponsored by two different concepts: "sargability" and "DDL triggers."

Sargability

#653206 : The optimizer should be able to understand the invertability of built-in intrinsics on dates for the purposes of index sargability

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.

#526431 : Make more functions SARGable

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.

#539631 : Find Potential Non-Sargable Where Clauses In Proc

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.

DDL Triggers

#299328 : INSTEAD OF DDL Triggers and DMF

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.

#243986 : Add "instead of" DDL triggers

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.