The SQL feature I’m still waiting for

September 10, 2019

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, 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.


This Post Has One Comment

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs