Inline UDF implicit conversion

Question

question

normg asked Jun 16, '20

Inline UDF implicit conversion

I have an inline table UDF with a varchar(3) input parameter. When I call the udf with a value from a table and look at the execution plan XML, I see implicit conversions and I cannot get rid of the implicit conversion. If I call the UDF with a hard coded value the implicit conversion is no longer present. In production, I will be calling UDF 100,000+ times and I want to avoid the performance hit associated with implicit conversion. Any and all help in this matter is greatly appreciated

Test Code

CREATE FUNCTION dbo.UDTF_Test
(
  @c_code AS VARCHAR(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT @c_code c_name
go
IF OBJECT_ID('tempdb..#test')IS NOT NULL
DROP TABLE #test
go
CREATE TABLE #test
(
  A VARCHAR(255)
)
INSERT INTO #test
SELECT 'bb'
SET STATISTICS IO,TIME ON
SELECT #test.*
, 
ITVF.c_name
FROM  #test
CROSS APPLY dbo.UDTF_Test( A )  ITVF
SET STATISTICS IO,TIME OFF

conversion udf execution plan

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-06-16T10:34:52Z

Kev Riley answered Jun 16, '20

You'll see implicit conversions as the input datatype is not varchar(3) - to avoid implicit conversions align the datatypes between the table and the input on the function

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

normg commented · Jun 16, 2020 at 02:01 PM

Thanks for your reply. When i do the following, I still get the implicit conversion.

SELECT #test.*

, ITVF.c_name

FROM #test

CROSSAPPLY dbo.UDTF_Test( cast( A as varchar(3))) ITVF

0 ·

Kev Riley ♦♦ normg commented · Jun 16, 2020 at 02:49 PM

What version of SQL is this? I don't see any implicit conversion when I run that, as there is an explicit conversion in the code. Where do you see it?

0 ·

Magnus Ahlkvist normg commented · Jun 16, 2020 at 02:49 PM

I don't see implicit conversion. Looking at my plan from the execution of your second query, I get this in the plan:

<ScalarOperator ScalarString="CONVERT(varchar(3),[tempdb].[dbo].[#test].[A],0)">
<Convert DataType="varchar" Length="3" Style="0" Implicit="false">

while in the first query I get:

<ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(3),[tempdb].[dbo].[#test].[A],0)">
  <Convert DataType="varchar" Length="3" Style="0" Implicit="true">

0 ·

normg commented · Jun 16, 2020 at 06:57 PM

Thanks, guys. So I took out the temp table and created a persisted table in SQL Server with the same structure as the temp table to mock production and I get implicit conversions. What am I missing...

0 ·

Magnus Ahlkvist normg commented · Jun 17, 2020 at 05:16 AM

Could you share relevant parts of the plan, either as screenshots showing where you have the implicit conversion, or copying relevant parts of the plan xml?

0 ·

question