dwain.c (8/16/2012)
Here's another way that is a bit faster than using PARSENAME.
If we speak for performance aspect, I think it is not parse name, rather it is replace impact, because of its design.
I rerun your test specifying collation explicitly like that
select
@HOLDER1 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),3),
@HOLDER2 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),2),
@HOLDER3 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),1)
from
#testEnvironment
and got those numbers
================================================================================
PARSENAME
================================================================================
Table '#testEnvironment____________________________________________________________________________________________________000000000043'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1875 ms, elapsed time = 1870 ms.
================================================================================
SUBSTRING
================================================================================
Table '#testEnvironment____________________________________________________________________________________________________000000000043'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2203 ms, elapsed time = 2199 ms.
I also noticed an interesting thing if you specify collation explicitly for substring example - it has no effect. Parsename wins.
But, if you create a table like:
CREATE TABLE #testEnvironment (c VARCHAR(100) COLLATE LATIN1_GENERAL_BIN)
than substring wins again, with this numbers in my tests: SUBSTRING-1334 ms VS PARSENAME 1676 ms.
So, I think, talking about performance we may conclude, that as usual, it depends =)