Do Functions Stop You From Using Indexes?

Say I’ve got a function in my WHERE clause:

If I have an index on DisplayName, will SQL Server use it? Sure:

Function in the WHERE clause

Function in the WHERE clause

Even though SQL Server can’t seek to “Brent Ozar,” it will still scan that entire index, along the way trimming the leading & trailing spaces from every user’s DisplayName.

But watch what happens when I change the SELECT from just getting DisplayName, to getting all of the fields:

No longer uses the index

No longer uses the index

Now, it stopped using the index – but why? The answer is in the estimated number of rows. In both plans, SQL Server estimated that it would find 62,224 rows matching LTRIM(RTRIM(DisplayName)) = ‘Brent Ozar’ – but it was wildly incorrect. That was an overestimation, and SQL Server believed that the resulting key lookups (to get the SELECT *) would be less efficient.

Note that I’m using the new SQL Server Management Studio 18 preview, which shows estimated versus actual rows on each operator in the query plan. I’m a huge fan of this – it really helps estimation problems pop right out at you.

So do functions stop you from using indexes?

  1. Yes, in the sense that you may end up using the indexes less efficiently, doing scans instead of seeks.
  2. No, in the sense that if the index perfectly covers the query, you can indeed still scan that covering nonclustered index rather than the entire table.
  3. But yes, if the index isn’t perfectly covering, because the estimates will likely be garbage, and SQL Server will think it’s going to return a ton of rows, and end up choosing the clustered index scan in order to avoid an imaginarily high number of key lookups.
Previous Post
Query Store And Cruel Defaults
Next Post
5 Ways to Change Execution Plans Without Tuning

5 Comments. Leave new

  • Toby Ovod-Everett
    January 11, 2019 3:57 pm

    Note that while the LTRIM in that query has an impact on the results, the RTRIM is superfluous. RTRIM only removes trailing spaces (not other forms of whitespace), and SQL Server effectively ignores trailing spaces when doing string comparison (more precisely, it space pads the shorter string until they are of the same length and then compares them). As a result, while RTRIM is useful for cleaning up data when returning results, in this case it is unnecessary in the WHERE predicate.

    Reply
  • John VanDamme
    January 18, 2019 7:16 am

    This behavior also occurs when creating persisted computed columns.

    Reply
  • I am a firm believer that most of the work in SQL is done in the DDL and not in the DML. I would have written this like so:

    CREATE TABLE Users
    (..,
    display_name VARCHAR(25) NOT NULL,
    CHECK( display_name = LTRIM(RTRIM(display_name),
    …);

    I am now guaranteed some certainty about what my data looks like. I also wonder when Microsoft will provide the ANSI/ISO standard TRIM() function that can do both directions and some other things that we have to kludge.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}