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.

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.

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

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)

SQL Function, SQL Scripts, SQL Server
Previous Post
SQL Server Performance Tuning – Upcoming Public Training
Next Post
Azure – Which One to Get – Standard Disks or Premium Disks

Related Posts

Leave a Reply