Warning about implicit convert

Question

I have table with 2 name columns:

CREATE TABLE Test
(
  TestID int identity primary key clustered
  , Name_Eng nvarchar(50)
  , Name_Nat nvarchar(50)
)

Now I need a query to get this Name column separated by ,, like this:

    DECLARE @NameColumns NVARCHAR(1024)

            SET @NameColumns = STUFF(
      (SELECT   ',' + 'Test.' + name AS [text()]
       FROM     ( SELECT    c.name
                  FROM      sys.columns c 
                            INNER JOIN sys.tables t ON t.object_id = c.object_id
                  WHERE     t.name = 'Test'
                            AND c.name LIKE 'Name_%'
                ) AS D
                FOR                  XML PATH('') ,
                                         TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1,
                                     N'')

select  @NameColumns

But this query has a warning in the execution plan:

Is there any way to remove this warning?

Answer 1

The warning is there because of the XML function value(). The second parameter to value() is what you want the value stored in the XML to be converted to. You could argue that this is not in fact an implicit conversion but a very explicit conversion since you are asking for it to happen. Perhaps something for a connect item to suggest to Microsoft.

Simplest way to reproduce what you see.

declare @X xml;
select @X.value('text()[1]', 'int');

Gives these two warnings.

<Warnings>
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0)" />
</Warnings>

As you can see you get it with int's as well and you get two of them for each invocation of value().

declare @X xml;
select @X.value('text()[1]', 'int'),
       @X.value('text()[1]', 'bit');

<Warnings>
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(bit,XML Reader with XPath filter.[lvalue],0)" />
    <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(bit,XML Reader with XPath filter.[value],0)" />
</Warnings>

The conversion is done in the Stream Aggregate operator that calculate the value like this.

MIN(CASE WHEN [@X] IS NULL 
      THEN NULL 
      ELSE 
      CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) 
        THEN CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0) 
        ELSE CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0) 
      END 
    END)

The result from the Table Valued function is returned in the lvalue or value column. The expression checks by using datalength to figure out where it should be fetched from and then converts it to your desired datatype.

Is there any way to remove this warning?

Yes there is. You remove the TYPE directive from the FOR XML PATH statement and you remove the call to the value() function. A side effect of doing that is that values you concatenate that contains characters that needs to be encoded in the XML like &<> will be encoded in your result.

Answer 2

While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in terms of estimation either).

I would write the query this way, taking care to never use varchar strings (which also means stop leaving out the N prefix on string literals), and also ensuring you use statement terminators:

DECLARE @NameColumns nvarchar(max); -- why 1024 when you use max below?

SET @NameColumns = STUFF(
  (SELECT   N',Test.' + name AS [text()] FROM    
    (
      SELECT c.name FROM sys.columns AS c 
        INNER JOIN sys.tables AS t 
          ON t.object_id = c.object_id
        WHERE t.name = N'Test'
        AND   c.name LIKE N'Name_%'
    ) AS D FOR XML PATH(N''),
    TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');

SELECT @NameColumns;

Even still, I don't believe there is any way to avoid the implicit convert without changing the output by avoiding TYPE/value() as Mikael suggested. I would say worry about this when you can actually prove that this has some material impact on the performance of the query. In my tests, the two different forms performed the same (we're talking sub-10ms every time), but of course if I have a table named Sales & Stuff then it becomes Sales & Stuff without the implicit convert.

Answer 3

The warning in the query plan means that because you have an implicit datatype conversion, SQL Server won't be able to accurately guess the correct number of rows returned, which in turn might lead to a less-than-optimal plan.

This is important in queries that have to perform well, normally because they work with a lot of data, but in your situation, this does not appear to be the case, as you're just querying sys.tables and sys.columns.

The short answer to your question is: it doesn't matter unless you have millions and millions of tables and columns in your database.