none
Type conversion warning RRS feed

  • Question

  • I get this error in execution plan:

    Type conversion in expression (CONVERT(varchar(10),[l].[Code],0)) may affect "CardinalityEstimate" in query plan choice; Type conversion in expression ([w].[wh_id]=CONVERT(varchar(10),[l].[Code],0)) may affect "SeekPlan" in query plan choice

    Query is:

    USE [MyFirstDB];

    CREATE TABLE #myWh (wh_id VARCHAR(10), type_id SMALLINT); ....

    DECLARE @sql NVARCHAR(4000);

    SET @sql='SELECT l.[Location Type] FROM [MySecondDB].dbo.[Location]l INNER JOIN #myWh w (NOLOCK) ON w.wh_id=l.Code COLLATE DATABASE_DEFAULT WHERE l.Availability=''''';

    EXEC sp_executesql @sql;

    Why I get warning for Type conversion if both columns are of the same type? They are only in different collation, since they are in different databases.

    Friday, June 14, 2019 9:24 AM

Answers

  • l.code has database collation. If I tell query to use database default collation it should use index on l.Code,

    "Should" and what actually happens when it comes to the optimizer is not always the same thing. Maybe you get the seek anyway. Maybe you don't. And in any case, you get the warning since you are casting the collation on the other column. I don't think the warning system is smart enough to say "it doesn't matter".

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by simonxy Tuesday, June 18, 2019 7:32 AM
    Monday, June 17, 2019 10:08 PM

All replies

  • The collation change counts as a type conversion in this context. Particularly, it has the same effect. If you have an indexed column with the collation Slovenian_CI_AS, and you tell SQL Server to evaluate the column as if the collation was Finnish_Swedish_CS_AS, this makes it impossible to seek the index, since the index is organised according to the rules for Slovenian and not for Swedish.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, June 14, 2019 9:38 PM
  • Erland,

    "w.wh_id=l.Code COLLATE DATABASE_DEFAULT"

    w.wh_id doesn't have any index and has the temp table collation (server collation) which is different than database collation.

    l.code has database collation. If I tell query to use database default collation it should use index on l.Code, only index w.wh_id can't be used since it has other collation (and also it can't be used since it doesn't exists anyway).

    This warning message is in place only if l.code index collation is different than database default, am I right?

    Monday, June 17, 2019 7:21 AM
  • Hi Simonx,

     

    The warning is informational, which is triggered by the implicit conversion used for the collation change. As you should already know, collation is inherited from database, and the usage of collation may affect "CardinalityEstimate" in query plan choice. If you insist in using index to seek, you'd better replace one collation with another one which share the same collation, instead of trying to convert. Alternatively, just leave them along.

     

    You can check below link for your reference:

    https://thomaslarock.com/2012/08/why-datatypes-matter-3-ways-they-can-hurt-performance/

     

     

    Regards,

    Sabrina


    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.

    Monday, June 17, 2019 8:36 AM
  • l.code has database collation. If I tell query to use database default collation it should use index on l.Code,

    "Should" and what actually happens when it comes to the optimizer is not always the same thing. Maybe you get the seek anyway. Maybe you don't. And in any case, you get the warning since you are casting the collation on the other column. I don't think the warning system is smart enough to say "it doesn't matter".

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by simonxy Tuesday, June 18, 2019 7:32 AM
    Monday, June 17, 2019 10:08 PM