Greg Snidow (1/13/2012)
Peter, I looked at the link you provided for the lookup table, and decided to create a permanent table to use in the function.
That's a good thing to do.
I also tweaked it to return only the final value, and to use a tally table instead of a recursive CTE. I'm not sure which is better, but I have never used a recursive CTE, so I wanted to stick with a solution I fully understood.
It's a perfectly valid argument to stick with a solution you fully understand, especially if you pick something up from the internet. However, in this case I would stay with the recursive CTE just because it performs better. Scalar functions do not perform very well. Besides, the SELECT @x = ...@x ... method seems to be unreliable (sorry, don't have details at hand, but maybe Paul is willing to jump in). Here's a quick test:
DECLARE @v-2 VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650C
SET STATISTICS TIME ON
PRINT '-- Scalar function'
select top 10000
dbo.fnAsciiToEbcdic(@V) V
from
sys.columns C1
cross join
sys.columns C2
PRINT '-- Recursive CTE'
SELECT TOP 10000
A2E.*
FROM
sys.columns C1
CROSS JOIN
sys.columns C2
CROSS APPLY
dbo.AsciiToEbcdic(@V) A2E
SET STATISTICS TIME OFF
Result:
-- Scalar function
(20000 row(s) affected)
SQL Server Execution Times:
CPU time = 1466 ms, elapsed time = 1545 ms.
-- Recursive CTE
(20000 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 252 ms.
Anyway, thanks for the very positive feedback.
Peter