dwain.c (11/21/2012)
Jeff Moden (11/21/2012)
I don't believe that COLLATE will help a STUFF (but I haven't tested it). Logically speaking, it should only help when string comparisons are being made.I didn't think so either. 🙂 That wasn't what I meant by impact.
It does seem to be helping the CHARINDEX in this case though, but sometimes I've found no effect.
These result I got when I remove COLLATE:
Using SUBSTRING and CHARINDEX
1953
Using PARSENAME
1936
Using PARSENAME with CROSS APPLY
1923
Using STUFF (Dwain) no check for a@b.com and empty
1046
Using STUFF (Dwain) with check for a@b.com and empty
2016
Using CLR
360
It's dramatic difference: 5 to 7 times slower than with binary collation.
At least that what I can see on my server (default collation Latin1_General_CI_AS)