carl.anderson-1037280 (4/6/2010)
I guess I have to read up on collations now!
Worth doing, but the reason it is important here is that the sorting and comparison rules are very complex for Windows collations, much less so for SQL collations, and pretty simplistic for binary collations. See https://connect.microsoft.com/SQLServer/feedback/details/512459/replace-function-extremely-slow-with-non-binary-windows-collation
PS> I replaced the SPACE(0) with '' - it didn't seem to change performance. What's the idea behind SPACE(0)??
I just find SPACE(0) more explicit and easier to read than ''.
The difference between '' and ' ' is not always easy to spot in scripts, whereas SPACE(0) is clearly not the same as SPACE(1) 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi