SQL Server Performance

Use of UPPER in where clause while using case insensitive colation

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by IDTX2, Oct 3, 2007.

  1. IDTX2 New Member

    We have an "off the shelf" software package that was recently purchased. While going through the SQL statements I captured in a profiler trace I noticed the package was making liberal use of the UPPER function on char and varchar predicates in the where clauses. However, the database is installed with the default case insensitive collation? After looking at some of the execution plans with and without the UPPER function I noticed that the same rows are returned, but with the UPPER the optimizer uses an INDEX SCAN and without the UPPER it uses an INDEX SEEK?
    Question #1 - Is there any valid reason to use the UPPER function in a where clause on a case insensitive colation? It is not being used for data display, just in the evaluation of predicates.
    Question #2 - Why does the use of the UPPER function cause an index scan instead of an index seek?
    Thanks
  2. Adriaan New Member

    It does an index scan because the UPPER() function is masking the actual data.
    It doesn't really matter if you have WHERE UPPER(MyColumn) = 'ABC' or WHERE REVERSE(MyColumn) = 'cba'. In both cases you're telling SQL Server to manipulate the data from the table, and so SQL Server has to read the entire table, or the entire index, manipulate the data, and finally apply the criteria.
    With WHERE MyColumn = 'ABC' there is no manipulation involved, so SQL Server can immediately do an index seek, which provides the best performance.
    Ground rule: do not embed columns in functions in WHERE clauses.
  3. IDTX2 New Member

    Thanks!
    I know Oracle has function based indexes, any corollary in SQL 2005?
    Since the package is an off the shelf purchase, I'm sure the stakeholders will be unwilling to modify the code/SQL.
  4. Adriaan New Member

    Function based indexes could probably be mimicked by adding a view with a column expression, and then adding an index on that column expression.
  5. IDTX2 New Member

    [quote user="Adriaan"]
    Function based indexes could probably be mimicked by adding a view with a column expression, and then adding an index on that column expression.
    [/quote]
    So an indexed view. Will the optimizer consider an indexed view without a query hint in Standard Edition SQL 2005?

Share This Page