SQL SERVER – Performance Observation of TRIM Function

Earlier I wrote a blog post about how to remove leading and trailing spaces with TRIM function. SQL SERVER – 2017 – How to Remove Leading and Trailing Spaces with TRIM Function? After writing this blog post lots of people asked me its impact on the performance of SQL Server.

Previously I had written similar blog posts, you can read them before reading this blog post further:

• SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video
• SQL SERVER – 2008 – Enhanced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds
• SQL SERVER – 2008 – TRIM() Function – User-Defined Function
• SQL SERVER – TRIM() Function – UDF TRIM()

Previously we have seen that whenever we use any function on the column used in WHERE condition, the performance is degraded. Most of the time when we use a function on the WHERE clause SQL Server have to apply that function to the entire column before the where a condition is evaluated. This is the reason, we usually do not see seek on the column. When SQL Server has to apply a function on an entire column of SQL Server, it has to scan the column which is usually (most of the time, but not always) expensive than seeking operation.

Let us run following three queries together. Please note that there is already an index on column FullName which is used in the WHERE condition.

USE WideWorldImporters
GO
SELECT PersonID, FullName
FROM [Application].[People]
WHERE FullName = 'Stella Rosenhain'
GO
USE WideWorldImporters
GO
SELECT PersonID, FullName
FROM [Application].[People]
WHERE LTRIM(RTRIM(FullName)) = 'Stella Rosenhain'
GO
USE WideWorldImporters
GO
SELECT PersonID, FullName
FROM [Application].[People]
WHERE TRIM(FullName) = 'Stella Rosenhain'
GO

You can clearly see from the execution plan in the resultset that, when we have not used any function on the WHERE condition, SQL Server is able to use an index seek to search for the data. However, whenever we use a function on the column used in the WHERE condition, SQL Server has to use an index scan.

Additionally, we can also learn from the resultset that function TRIM is no better than our old school workaround of LTRIM and RTRIM.

Reference: Pinal Dave (https://blog.sqlauthority.com)