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)