THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server) and leader of the SQL User Group in Adelaide, Australia. Rob is also a Director of PASS, and runs training courses around the world in SQL Server and BI topics.

Function Invertability for SARGability

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.

Published Thursday, March 24, 2011 12:42 PM by Rob Farley
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

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.

March 23, 2011 10:59 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement