Need help on to execution plan in SQL Server in Type Conversion

Question

I am getting below warnings in execution plan. Please some provide suggestions on how to resolve this error.

Type conversion in expression (CONVERT(int,STRING_SPLIT.[value],0)) may affect "CardinalityEstimate" in query plan choice

Above warning in seen for below SQL Statement

SELECT TE.value AS GroupID
    ,ISNULL(TM.TotalCount, 0) AS GroupCount
FROM #tblWorkQueueDocuemnt TM
RIGHT OUTER JOIN (
    SELECT CONVERT(INT, value) AS value
    FROM STRING_SPLIT('1,2,3,4', ',')
    ) TE ON TM.GroupID = TE.value

#tblWorkQueueDocuemnt : Statement for Create Table. CREATE TABLE #tblWorkQueueDocuemnt (GroupID INT, TotalCount INT)

score 3 · Answer 1 · answered Oct 17 '20 at 08:42

Basically, the warning message indicates that, because of the type conversion, an index cannot be used in the derived table. I don't think that it really matters here, because the derived table itself is based on a parsed literal string - so there cannot be an index whatsoever.

As a side note: do you really need to pass this value as a literal CSV string? If would be simpler to build a proper derived table with values(), like so:

select te.value as groupid, coalesce(tm.totalcount, 0) as groupcount
from (values (1), (2), (3), (4)) as te(groupid)
left join #tblworkqueuedocuemnt tm on tm.tm.groupid = te.value

Incidently, this would avoid the warning (there is no conversion needed, as values are given with the proper datatype to start with). But, again, I don't think you shoud worry about that warning.

score 1 · Answer 2 · answered Oct 17 '20 at 08:38

It's an old thing they have whenever they have an implicit conversion.

In instances where you are doing an implicit conversion on a field, it may not be able to use the index. Similar to if you did an explicit CAST or CONVERT on it.

If it's running fine and has no problems, ignore it.

See e.g., https://www.brentozar.com/archive/2018/10/we-need-to-talk-about-the-warnings-in-your-query-plans/

In your case, you could explicitly cast the values after the string_split as ints, if desired. It is technically cleaner but probably won't improve performance at all.

score 1 · Answer 3 · answered Jan 26 '23 at 15:10

With this query:

SELECT CONVERT(INT, value) AS value
FROM STRING_SPLIT('1,2,3,4', ',')

I get the same warning:

But changing CONVERT(INT, value) to PARSE(value AS int):

SELECT PARSE(value AS int) AS value
FROM STRING_SPLIT('1,2,3,4', ',')

Removes the warning:

Need help on to execution plan in SQL Server in Type Conversion

3 Answers3