|
By default, SQL Anywhere databases support case-insensitive string comparisons. Occasionally the optimizer may encounter queries where the user is explicitly forcing text conversion through the use of the UPPER, UCASE, LOWER, or LCASE built-in functions when such conversion is unnecessary. SQL Anywhere automatically eliminates this unnecessary conversion when the database's collation sequence permits it. An extra benefit of eliminating the case translations in the predicates is the transformation of some of these predicates into sargable predicates, which can be used for indexed retrieval of the corresponding table.
Consider the following query:
SELECT * FROM Customers WHERE UPPER(Surname) = 'SMITH';
On a case insensitive database, this query is rewritten internally as follows, so that the optimizer can consider using an index on Customers.Surname:
SELECT * FROM Customers WHERE Surname = 'SMITH';