I recently had a VERY interesting experience while cleaning up some data. I had about 8GB of SQL Server table data - all nvarchar(max) stuff. The tables needed to be compared to find the differences so the data really did need to be cleaned up. For a while I used an UPDATE statement that contained the REPLACE() function in it. It worked well, but took over an hour to complete. I began to think that there HAD to be a better way to do this. All I was really concerned about was trimming white space off the front and end of the field. I thought I would try a combination of the RTRIM() and LTRIM() functions just to see what would happen like so:
UPDATE table
SET field1 = LTRIM(RTRIM(field1))
This one simple change shaved 40 minutes off my job! Why? Because REPLACE() has to crawl across each field character by character to find each what needs to be replaced. The TRIM functions are only concerned about white space at the beginning and end of the field and so, in this case, they are much faster. There is nothing quite like using the right tool for the job. Many thanks to a very wise
DBA for explaining what was going on.
