Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

implicit conversion warning

Is there any way to remove this warning?

share|improve this question
2  
I would suggest you to change value('.[1]', 'VARCHAR(MAX)'), 1, 1 to value('.[1]', 'NVARCHAR(MAX)'), 1, 1 since you have @NameColumns defined as NVARCHAR(1024). I tried on my test env and I am not getting any implicit conversion warning. Is the server and database collation same ? – Kin Jan 19 at 14:26
1  
@Kin Just FYI I still couldn't get rid of the implicit convert (though on your system you may not get a warning in the graphical plan depending on version). Check the Expressions tab in Plan Explorer, you should still see them for a stream aggregate and a compute scalar even with your changes. – Aaron Bertrand Jan 19 at 15:51
    
@AaronBertrand I do see it in Plan Explorer (as usual its awesome). I was just comparing SSMS plans that I got and from the OP. – Kin Jan 19 at 16:07
up vote 8 down vote accepted

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.

share|improve this answer
    
Nice digging, but materially there is no big advantage to removing the warnings. – Aaron Bertrand Jan 19 at 22:02
    
@AaronBertrand I agree. In this case it produces only one row result with one execution of the branch in the plan that does the call to the TVF. But if you have many rows returned you will see a difference in performance but not so much that it outweighs the need for correct result :). Only if the task is to concatenate integers I would consider using the version without TYPE and value(). And I doubt that removing the conversion is the main reason for better performance. It probably is faster because there is no need to call the TVF. – Mikael Eriksson Jan 20 at 8:36

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 &amp; Stuff without the implicit convert.

share|improve this answer
    
Aaron, Thanks for you reply, but I have tried this already, and the warring still accused. – Artashes Khachatryan Jan 21 at 8:16
    
@ArtashesKhachatryan If you'll read closely you'll note that I didn't suggest that my solution would remove all of the converts. Please make sure you're okay with how the output changes - as I said, I don't think any performance issue here is because of that warning. – Aaron Bertrand Jan 21 at 14:24

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.