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:

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

SQL SERVER - Performance Observation of TRIM Function trimfunction1-800x472

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)

, ,
Previous Post
SQL SERVER – Compatibility Level 80 and Table Hint Behavior
Next Post
SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE

Related Posts

3 Comments. Leave new

  • Yes, but the first query is different from the second and third. You can’t really compare. Imagine this is your data – you’d get different results:

    CREATE TABLE People (
    PersonID int IDENTITY(1,1)
    , FullName varchar(50)
    )

    INSERT INTO People (FullName)
    VALUES
    (‘Stella Rosenhain’)
    , (‘ Stella Rosenhain’)
    , (‘Stella Rosenhain ‘)
    , (‘ Stella Rosenhain ‘)

    Sometimes you just have to suck it up and use LTRIM, RTRIM or TRIM.

    John

    Reply
  • Which one is better between TRIM and LIKE in terms of performance

    Reply

Leave a Reply

Menu