My good friend Simon Sabin used the term ‘invertability’ on a Connect item he logged today.
Essentially, Simon’s noticed that there are lots of people that use year(someDate), but that the system doesn’t understand that this function doesn’t affect the order of the items in the index. month(someDate) does, but if you’re already using year(someDate), then the combination of the two doesn’t change.
This is one of the keys to SARGability, which I’ve written about before, like at http://bit.ly/sargability. I’ve also raised a Connect item myself about it.
However, the term ‘invertability’ is interesting, and ties into the Inverse Predicates concept that I’ve also used before, like at http://bit.ly/inversepredicates. The idea is that you might have applied a function to a column, creating a predicate that isn’t sargable, but if you (or the system) can tell how to invert it, then you can make a predicate that can be easily handled by the Query Optimizer. Currently, the system doesn’t understand the invertability of all the functions (even easy ones like the YEAR function), but it’s something which would make SQL a lot faster if it did.
I’m going to let you read those various posts yourself, and encourage you to vote for Simon’s connect item. But as well as that, I’m going to encourage you to consider the SARGability of the predicates in your query.
Edit: Simon’s written a post on this now.
This Post Has One Comment
Rob,
I don’t think this is such a good idea. The requested feature is not a must have, there is a very easy workaround. It is a nice to have feature we can live without.
Adding non-essential features slowly kills systems. Ray Ozzie’s parting memo to Microsoft is a very good read.
More specifically, the following discussion on Bart Duncan’s blog illustrates how complex it is to make any changes to a complex system:
http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/
More features does not always mean a better system. In many cases less is more. Just look at the lightning fast Chrome. Look at the recently released Mozilla, which is almost not advertised at all, yet downloaded by millions. Look at Git – it is very simple, not much advertised, and its popularity grows exponentially.