Say I’ve got a function in my WHERE clause:
WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar';
If I have an index on DisplayName, will SQL Server use it? Sure:
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:
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?
- Yes, in the sense that you may end up using the indexes less efficiently, doing scans instead of seeks.
- 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.
- 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.