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
