Mr. Brian Gale wrote:
This feels almost like an undocumented "feature" (similar to quirky updates)... I know I would be a bit hesitant in how I used this and would probably continue to stick with my nested REPLACE's as I know how those work today and in the future.
But I did learn something as I expected a different answer on this one than was the actual answer! Hopefully it is consistent across all SQL versions (tested it on 2017 and it worked as described). Would suck to implement this only to find the behavior being different on different versions though or having it change in a future version.
It's not a quirk although it's not specifically documented in the MS documentation. It's sometimes referred to as "overloading a variable" or just "overloading". This is one of the things that T-SQL does very well where Oracle will give you an error.
I use this method at work to "normalize" addresses according to postal standards in-so-far as abbreviations go and it's nasty fast for what it does even though it's used in a Scalar Function especially if you use a binary collation.
Also, and to clarify, the "Nested Replace" method is even faster and can be used in iTVFs. The reason why I used it in a Scalar Function was to use it in a persisted computed column in the days before I understood that using Scalar Functions in computed columns causes any and all queries to go single threaded even if they don't refer to the column. See Brent Ozar's article on that subject, which I've individually confirmed as being true and am in the process of replacing.
It also turns out to be an even bigger problem in other areas, as well... like parallel index rebuilds and DBCC CHECKDB. See Erik Darling's post on that.
And the "recently patched" thing Eric is talking about is for 2012 and 2014 where online rebuilds could sometime cause corruption of your clustered indexes. Here's the info on that.
And people wonder why migrating to 2019 has me concerned even though it's been out for 3 years. 🙁