Most efficient way to call same Table-Valued Function on multiple columns in a Query

I am trying to tune a query where the same table-valued function (TVF) is called on 20 columns.

The first thing I did was convert the scalar function into an inline table-valued function.

Is using CROSS APPLY the best performing way to execute the same function on multiple columns in a query?

A simplistic example:

SELECT   Col1 = A.val
        ,Col2 = B.val
        ,Col3 = C.val
        --do the same for other 17 columns
        ,Col21
        ,Col22
        ,Col23
FROM t
CROSS APPLY
    dbo.function1(Col1) A
CROSS APPLY
    dbo.function1(Col2) B
CROSS APPLY
    dbo.function1(Col3) C
--do the same for other 17 columns

Are there better alternatives?

The same function can be called in multiple queries against X number of columns.

Here's the function:

```
CREATE FUNCTION dbo.ConvertAmountVerified_TVF
(
@amt VARCHAR(60)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH cteLastChar
AS(
SELECT LastChar = RIGHT(RTRIM(@amt), 1)
)
SELECT
AmountVerified = CAST(RET.Y AS NUMERIC(18,2))
FROM (SELECT 1 t) t
OUTER APPLY (
SELECT N =
CAST(
CASE
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0)-1
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0) >0
THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0)-1
WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0) >0
THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0)-1
ELSE
NULL
END
AS VARCHAR(1))
FROM
cteLastChar L
) NUM
OUTER APPLY (
SELECT N =
CASE

FIRST: it should be mentioned that the absolutely fastest method of getting the desired results is to do the following:

• Migrate data into either new columns or even a new table:

• New column approach:

• Add new columns as {name}_new to the table with the DECIMAL(18, 3) datatype

• Do a one-time migration of the data from the old VARCHAR columns to the DECIMAL columns

• rename the old columns to {name}_old

• rename new columns to be just {name}

• New table approach:

• Create new table as {table_name}_new using DECIMAL(18, 3) datatype

• Do a one-time migration of the data from current table to new DECIMAL-based table.

• rename old table to _old

• remove _new from new table

• Update app, etc to never insert data encoded in this manner

• after one release cycle, if no problems, drop old columns or table

• drop TVFs and UDF

• Never speak of this again!

THAT BEING SAID:
You can get rid of a lot of that code as it is largely unnecessary duplication. Also, there are at least two bugs that cause the output to sometimes be incorrect, or sometimes throw an error. And those bugs were copied into Joe's code as it produces the same results (including the error) as the O.P.'s code. For example:

-
These values produce a correct result:

00062929x
00021577E
00000509H

-
These values produce an incorrect result:

00002020Q
00016723L
00009431O
00017221R

-
This value produces an error:

00062145}
anything ending with "}"

Comparing all 3 versions against 448,740 rows using SET STATISTICS TIME ON;, they all ran in just over 5000 ms of elapsed time. But for CPU time, the results were:

• O.P.'s TVF: 7031 ms

• Joe's TVF: 3734 ms

• Solomon's TVF: 1407 ms

SETUP: DATA

The following creates a table and populates it. This should create the same data set across all systems running SQL Server 2017 since they will have the same rows in spt_values. This helps provide a basis of comparison across other people testing on their system since randomly generated data would factor into timing differences across systems, or even between tests on the same system if the sample data is regenerated. I started with the same 3 column table as Joe did, but used the sample values from the question as a template to come up with a variety of numeric values appended with each of the possible trailing character options (including no trailing character). This is also why I forced the Collation on the columns: I didn't want the fact that I am using a binary-Collation Instance to unfairly negate the effect of using the COLLATE keyword to force a different Collation in the TVF).

The only difference is in the ordering of the rows in the table.

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE dbo.TestVals
(
  [TestValsID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  [Col1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS NOT NULL,
  [Col2] VARCHAR(50) COLLATE Latin1_General_100_CI_AS NOT NULL,
  [Col3] VARCHAR(50) COLLATE Latin1_General_100_CI_AS NOT NULL
);

;WITH cte AS
(
  SELECT (val.[number] + tmp.[blah]) AS [num]
  FROM [master].[dbo].[spt_values] val
  CROSS JOIN (VALUES (1), (7845), (0), (237), (61063), (999)) tmp(blah)
  WHERE val.[number] BETWEEN 0 AND 1000000
)
INSERT INTO dbo.TestVals ([Col1], [Col2], [Col3])
  SELECT FORMATMESSAGE('%08d%s', cte.[num], tab.[col]) AS [Col1],
       FORMATMESSAGE('%08d%s', ((cte.[num] + 2) * 2), tab.[col]) AS [Col2],
       FORMATMESSAGE('%08d%s', ((cte.[num] + 1) * 3), tab.[col]) AS [Col3]
  FROM    cte
  CROSS JOIN (VALUES (''), ('{'), ('A'), ('B'), ('C'), ('D'), ('E'), ('F'),
              ('G'), ('H'), ('I'), ('}'), ('J'), ('K'), ('L'), ('M'), ('N'),
              ('O'), ('P'), ('Q'), ('R'), ('p'), ('q'), ('r'), ('s'), ('t'),
              ('u'), ('v'), ('w'), ('x'), ('y')) tab(col)
  ORDER BY NEWID();
-- 463698 rows

SETUP: TVF

GO
CREATE OR ALTER FUNCTION dbo.ConvertAmountVerified_Solomon
(
    @amt VARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

    WITH ctePosition AS
    (
        SELECT CHARINDEX(RIGHT(RTRIM(@amt), 1) COLLATE Latin1_General_100_BIN2,
                             '{ABCDEFGHI}JKLMNOPQRpqrstuvwxy') AS [Value]
    ),
    cteAppend AS
    (
        SELECT pos.[Value] AS [Position],
               IIF(pos.[Value] > 0,
                      CHAR(48 + ((pos.[Value] - 1) % 10)),
                      '') AS [Value]
        FROM   ctePosition pos
    )
    SELECT (CONVERT(DECIMAL(18, 3),
                    IIF(app.[Position] > 0,
                           SUBSTRING(RTRIM(@amt), 1, LEN(@amt) - 1) + app.[Value],
                           @amt))
                        / 100. )
                    * IIF(app.[Position] > 10, -1., 1.) AS [AmountVerified]
    FROM   cteAppend app;
GO

Please note:

• I used a binary (i.e. _BIN2) Collation which is faster than a case-sensitive Collation as it does not need to account for any linguistic rules.

• The only thing that really matters is the location (i.e. the "index

StackExchange Database Administrators Q#205341, answer score: 8