Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being found.
I'm a bit new to this, but my understanding of Sargable and Non-Sargable SQL is that the offending function is operating on the column and thus obstructing the index (assuming one is present) which leads to a scan of the column and a performance hit. The code has several functions in the input, lots of minor stuff, like
Where a.date between 'inputdate' and dateadd(day,1,'inputdate')
The DBA has taken the stance that any query which contains a function in the WHERE
clause is non-saragable. I'm going to drop the issue with him because arguing will get me nowhere except a pissed off admin and my access limited.
But for the sake of knowledge, is there a case where he's correct and any function present in the WHERE
clause is a problem, regardless of function or placement?
Best Answer
As ypercube commented
You can verify this by:
Once you've got the evidence, I suggest you do take it up with the DBA. However, don't get into an argument. Just show the test and data that demonstrates the index is used.
The point is you don't want to be forced to bend over backwards to avoid non-existant issues.
Fortunately in the case you demonstrated it won't be a problem to move the functions outside the query. E.g.
In fact, the above may even be more maintainable in the long run.
However, there will come a time when you have something that cannot be trivially changed according to the DBA's wishes. Such as:
The only way to get this function out of the WHERE clause would be to precalculate another column
b.NextDay
. Which is exactly why you need the DBA's to understand Sargability correctly. I.e. that the above:a.date
.b.date
.If you really can't get buy-in from the DBA's perhaps the following will work and bypass their cargo-cult rules:
The optimiser will almost certainly optimise this in the same way as if the function were in the
WHERE
clause, so you shouldn't get a performance knock. But it's certainly an unnecessary reduction in maintainability.