none
Type conversion RRS feed

  • Question

  • I have WHERE clause inside a query:

    USE db1;

    DECLARE @test varchar(20);
    SET @test='CHANNEL';

    CREATE TABLE #test(myColumn varchar(20));

    INSERT INTO #test(myColumn)
    SELECT dd.myColumn
    FROM db2.dbo.myTable as dd
    WHERE dd.myColumn=@test


    MyColumn(varchar(20)) is in other database table. DB where query runs has different collation - also temp db.
    I get this warning:

    Type conversion in expression (CONVERT_IMPLICIT(varchar(20),[dd].[myColumn],0)) may affect "CardinalityEstimate" in query plan choice.

    If I remove "INSERT INTO #test(myColumn)" table than this warning is gone.
    Or If I create #test table with "myCollation" collation, the warning is also gone:

    CREATE TABLE #test(myColumn varchar(20) COLLATE myCollation);

    So, type conversion is happening at insert statement - so, it can't affect query plan choice, since insert statement is always the last part of query plan. Plan is always the same.

    Is there some cardinality estimate for insert, which is not visible in execution plan?
    I think in this case warning is not needed - it only confuse developers.

    Monday, December 3, 2018 12:34 PM

All replies

  • Yes

    If there's a difference between source collation and collation of the column to which you're trying to insert there will be a type conversion operation

    If you want you may override the collation before inserting the value like

    INSERT INTO #test(myColumn)
    SELECT dd.myColumn COLLATE YourCollation
    FROM db2.dbo.myTable as dd
    WHERE dd.myColumn=@test

    where YourCollation represents collation of myColumn

    I think in the above case since you use # table the difference may be in the collation between your db and tempdb


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ashin_c Thursday, December 6, 2018 8:17 AM
    Monday, December 3, 2018 12:44 PM
  • Hi simonxy,

    Per your description , I think one possibility  is that the issue may be related to the difference of the data types of the column 'myColumn' in table 'dbo.myTable ' and  the column 'myColumn' in table '#table'.

     

    Could you please show us your table structure of the table 'dbo.myTable' ? Maybe the data type  of the column 'myColumn' in table 'dbo.myTable ' has a lower precedence than the data type  of  the column 'myColumn' in table '#table'. It will make that column to convert it into varchar(20). So SQL Server will show you the warning  and it hope that you will pay more attention to avoid some errors.

     

     There is an article  which will show you why SQL Server shows you a warning clearly, please refer to it : https://blog.sqlauthority.com/2010/10/08/sql-server-simple-explanation-of-data-type-precedence/ You can see that in query execution plan it will convert your column.

     

    Also , I don't think that the warning is not needed. Many developers may not have noticed this problem, and the warning  alerts them to the problem.

     

    By the way , you might try change varchar(20) into the data type  of the column 'myColumn' in table 'dbo.myTable ' to see if it satisfies your requirement.

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ashin_c Thursday, December 6, 2018 8:17 AM
    Tuesday, December 4, 2018 6:16 AM
  • Thank you Rachel,

    very good article.
    ButI know most of it.

    To clarify: myColumn is varchar datatype, @test is also varchar data type, all datatypes are the same.

    If I remove INSERT and write only SELECT:

    SELECT dd.myColumn 
    FROM db2.dbo.myTable as dd
    WHERE dd.myColumn=@test

    I don't get this warning. So, the warning must be because myColumn has different collation than #test when inserting.
    But it has nothing to do with "CardinalityEstimate" in query plan choice. That was my original question.
    Query plan would be the same even if my collation in temp table would be equal to myColumn collation.
    So, why "CardinalityEstimate" warning if it has nothing to do with it?

    Thursday, December 6, 2018 11:46 AM
  • Hi simonxy,

    Thank you for your reply.

     

    I am afraid that I could not reappear your situation. Could you please share us  table structure of the table 'db2.dbo.myTable' and some simple data?

     

    As Visakh16 said that 'If there's a difference between source collation and collation of the column to which you're trying to insert there will be a type conversion operation', the warning just a warning and it  just suggests that you might affect your "CardinalityEstimate", not necessarily that it will happen. If it doesn't affect, then you don't have to worry about it.

     

    Hope it can help you .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 7, 2018 6:11 AM