locked
Deterministic function call (lower) might cause an unnecessary table scan RRS feed

  • Question

  • From Microsoft Doc :

    "

    In a WHERE predicate, a function call is deterministic if its value does not depend on the selected data. Such calls could cause unnecessary table scans, which decrease database performance."

    Are there any example to show different when using deterministic function may cause table scans ??

    Friday, August 24, 2018 2:01 AM

Answers

  • why with the recommendation can solve this problem ?

    "To resolve this issue, you can assign the result of the call to a variable that you use in the WHERE predicate"

    I can't say that I get the impression that the person who wrote this was in full understanding of what he or she was talking about.

    So the SELECT * FROM Orders WHERE upper(CustomerID) = 'ALFKI' may cause Custer index scan  or index seek , all up to the optimizer to choose ?

    Yes. If the collation is case-sensitive, it will always be a scan, because the index is not usable. With a case-insentitive collation, it is as if upper was not there. You will get a CI scan or an index seek depending how many rows the optimizer thinks the condition will hit.

    • Marked as answer by sakurai_db Friday, August 31, 2018 2:31 AM
    Thursday, August 30, 2018 9:29 PM

All replies

  • Where's that from?  I'm not sure what it means, or if it's accurate.

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, August 24, 2018 2:11 AM
  • I use static code analysis from visio studio 2017 and one rule is

    SR0015 : Extract deterministic function calls from where predicates.

    Friday, August 24, 2018 3:12 AM
  • That is a piece of nonsense with quite some truth in it.

    In my book, a deterministic function is one that returns the same value for the same input. So lower() is deterministic. getdate() is an example of a non-deterministic function. Furthermore, for the topic they are discussing determinisim has nothing to do with - it does not get any better if the function is undeterministic.

    What it should say is "don't entangle an indexed column in an expression, because the index cannot be used".

    Here is one example that I just tested:

    SELECT * FROM Orders WHERE upper(CustomerID) = 'ALFKI'

    This resulted in a clusterded index scan on Orders. Because my database has a case-sensitive collation, the index is not sorted in a way that makes it usable for this query. I also tested in a different database with a case-insensitive collation, and the optimizer was smart enough in this case to realise that upper had no importance and seeked the index. But there are many other cases where there is no logical obstacle to use the index, but where the optimizer do not have any rule to sort out the situation. When in doubt, test.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 24, 2018 7:39 AM
  • SELECT * FROM Orders WHERE CustomerID = 'ALFKI'

    will do index seek in case-sensitive collation ?

    ALFKI is very selective or not?

    Friday, August 24, 2018 5:48 PM
  • Friday, August 24, 2018 6:03 PM
  • SELECT * FROM Orders WHERE CustomerID = 'ALFKI'

    will do index seek in case-sensitive collation ?

    Yes, it may do a seek. I say may because it depends on the data.

    ALFKI is very selective or not?

    In the Northwind database from where I drew this examples ALFKI has 15 orders of 830, so the optimizer settles for the index.

    Friday, August 24, 2018 9:21 PM
  • why with the recommendation can solve this problem ?

    "To resolve this issue, you can assign the result of the call to a variable that you use in the WHERE predicate"

    So the SELECT * FROM Orders WHERE upper(CustomerID) = 'ALFKI' may cause Custer index scan  or index seek , all up to the optimizer to choose ?

    Thursday, August 30, 2018 9:05 AM
  • why with the recommendation can solve this problem ?

    "To resolve this issue, you can assign the result of the call to a variable that you use in the WHERE predicate"

    I can't say that I get the impression that the person who wrote this was in full understanding of what he or she was talking about.

    So the SELECT * FROM Orders WHERE upper(CustomerID) = 'ALFKI' may cause Custer index scan  or index seek , all up to the optimizer to choose ?

    Yes. If the collation is case-sensitive, it will always be a scan, because the index is not usable. With a case-insentitive collation, it is as if upper was not there. You will get a CI scan or an index seek depending how many rows the optimizer thinks the condition will hit.

    • Marked as answer by sakurai_db Friday, August 31, 2018 2:31 AM
    Thursday, August 30, 2018 9:29 PM
  • thanks.~ let me try

    Friday, August 31, 2018 2:31 AM