Say I’ve got a function in my WHERE clause:
|
1 2 3 |
SELECT DisplayName FROM dbo.Users 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.
5 Comments. Leave new
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.
Toby – while the RTRIM may be unnecessary to achieve the results you want, it’s a common practice whenever people use LTRIM that they also use RTRIM. And when they do, it does indeed have an effect on the plan, as you can see here:
https://www.brentozar.com/pastetheplan/?id=BJq92PDfN
I hear what you’re saying – people shouldn’t do it – but if only people would never do things they shouldn’t do. 😉
This behavior also occurs when creating persisted computed columns.
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.
Good news man! It came out in SQL Server 2017:
https://docs.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-2017