This month’s T-SQL Tuesday has me caught slightly unawares. I’m in the UK, having come over for SQLBits, and I’m left writing this with only a few hours to go.
The theme this time is on misconceptions in SQL, and this fits nicely with SARGability – one of the topics that I presented on at SQLBits a week and a half ago. I often see people refer to SARGability as making sure that the left-hand side of the predicate is untouched. In other words, suggesting that something like this would be fine:
|
1 |
WHERE someColumn = someFunction(someValue) |
whereas something like this would not:
|
1 |
WHERE someOtherFunction(someColumn) = someValue |
As with most misconceptions, there’s an element of truth to this, but it definitely doesn’t tell the whole story.
If you have an index on someColumn, then great – the first predicate should let you find the value you want in that index nicely. Then you have other questions about whether the index is suitable or not, but that’s for a different post. On the other hand, if you don’t have an index, then the system is going to scan the table (or at least some less suitable index) in both scenarios. Applying the function to each column is painful in the second one, but SARGability is about producing seeks instead of scans, and these would both cause scans.
But how often do you write predicates like this. Normally, we have something more like:
|
1 |
ON x.someColum = y.someOtherColumn |
…and this naturally causes a question. Do we want to be looking up the values in x, or in y? What needs to be SARGable here, the left or the right? It’s very easy to assume the left, but is it really going to be best for you? SQL Server doesn’t care which way around you’ve written your predicate, so don’t hold onto the ‘left-hand side’ idea longer than the end of this post. In fact, think about the way you want your query to be executed, and ask yourself where your SARGability should actually be looking.
It’s all a matter of perspective…
