The SQL feature I’m still waiting for

This month Kevin Chant (@kevchant) challenges us to write about our fantasy SQL feature. And so I’m going to reiterate something that I had listed as a Connect item many years ago. It got quite a lot of upvotes, but was never picked up.

That feature was about making more predicates SARGable, by recognising when helper predicates could be leveraged to help performance. I wrote about it at http://blogs.lobsterpot.com.au/2010/01/22/sargable-functions-in-sql-server/, and the Connect item can be seen on the ‘wayback machine’ here.

The idea of this is to address the problem that people have when they write a predicate involving two date columns like “WHERE DATEDIFF(day, s.SomeDate, o.OtherDate) > 3”. This means that the number of days between the values in those two date columns must be more than 3, but could also be written as “WHERE s.SomeDate < DATEADD(day, -3, o.OtherDate)” or “WHERE o.OtherDate > DATEADD(day, 3, s.SomeDate)”. I appreciate that if you’re considering columns that also involve a time component then these aren’t exactly equivalent (in the first I’d need to convert o.OtherDate to a date type first, in the second I’d need to convert s.SomeDate to a date type, use 4 instead of 3, and make it >=), but I’m thinking about this from an index usage scenario.

If we have an index on s.SomeDate and we know o.OtherDate before the join is performed, then we might get a nice Index Seek operation to quickly find the rows in table ‘s’ that have SomeDate earlier than 3 days prior to o.OtherDate, but only if we’re using the first of those alternatives that I mentioned. If we know s.SomeDate and have an index on o.OtherDate, we might get a seek if we using the second alternative.

One of the query-tuning tricks I do is to introduce extra predicates to help indexes be used better. If I see a predicate that isn’t SARGable, I can often introduce helper predicates myself by adding them to the query. If I had all three of these predicates in my query, I haven’t changed the logic at all (again, assuming I understand the data types), but might have given the SQL Query Optimizer enough to do a better job of running this query.

And these helper predicates I add don’t have to be exact – they might just reduce the range. For example, even if I just filtered s.SomeDate < o.OtherDate, it might help significantly. If I can’t figure out an exact-enough predicate, well that’s not a problem, because I still have the original predicate. So long as I’m not filtering out more than the original one, then my logic is okay.

I just think that Microsoft could build some of this into the product.

@rob_farley