SQL Server – Performance Comparison of Function Trim and LTRIM(RTRIM)

While delivering SQL Server Performance Tuning Practical Workshop I recently received a question which is related to performance comparison of Function Trim and LTRIM/RTRIM.

Regarding the trim function, I have previously blogged in the following blog post and I request you to read the same before continuing the demonstration from this blog.

• 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()
• SQL SERVER – 2017 – How to Remove Leading and Trailing Spaces with TRIM Function?
• SQL SERVER – Performance Observation of TRIM Function

Noe let us start with a simple example where we will compare the performance of LTRIM/RTRIM with TRIM function which is newly introduced in SQL Server

-- Create a sample table
CREATE TABLE Test (ID INT, Col1 CHAR(100))
GO
SET NOCOUNT ON
INSERT Test (ID, Col1)
VALUES (1,' a ')
GO 1000
INSERT Test (ID, Col1)
VALUES (1,' b ')
GO
-- Create a nonclustered index
CREATE NONCLUSTERED INDEX IX_Test_Col1 ON Test (Col1);
GO
-- SELECT TEST
SELECT ID
FROM Test
WHERE LTRIM(RTRIM(Col1)) = 'b'
GO
SELECT ID
FROM Test
WHERE TRIM(Col1) = 'b'
GO
SELECT ID
FROM Test
WHERE Col1 = ' b '
GO
-- cleanup
DROP TABLE Test
GO

Now during the test, we have three select statement wherein the two cases we are using LTRIM/RTRIM as well as TRIM function. Let us compare the performance of the same with the select statement where we have not used any function in the WHERE clause.

It is very clear that if we use any function on the column used in the WHERE clause we are bound to get poor performance.

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