none
Type conversion in expression - Warning RRS feed

  • Question

  • Hi All,

    Getting Type Conversion Error for Select statement.

    LEFT OUTER  JOIN PART_LIST p2 ON e.status  = p2.PART
     AND p2.PART_TYPE = 'ABC'

    Type conversion in expression (CONVERT_IMPLICIT(nvarchar(10),[e].[STATUS],0)) may affect "CardinalityEstimate" in query plan choice

    e.status = varchar(10)

    p2.part = nvarchar(100)

    Please, assist.


    • Edited by KG2014S Monday, February 24, 2014 9:58 PM
    • Moved by Kalman TothModerator Tuesday, February 25, 2014 4:46 PM Not database design
    Monday, February 24, 2014 9:03 PM

Answers

All replies

  • Hello,


    The two data types are not the same. One includes Unicode characters the other doesn’t. Try to convert the NVARCHAR to CHAR before joining them, or change the data type at the table level to match on both tables.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, February 24, 2014 10:12 PM
    Moderator
  • The Table design can't be modified. Any other solution ?

    I did try CONVERT but still the warning.

    JOIN PART_LIST  p2 ON (CONVERT(nvarchar(10),e.status,0))  = p2.PART

    Type conversion in expression (CONVERT(nvarchar(10),[e].[STATUS],0)) may affect "CardinalityEstimate" in query plan choice

    Monday, February 24, 2014 10:24 PM
  • Hello,

    What about using CAST(p2.part as varchar(10))?

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by KG2014S Sunday, June 8, 2014 2:26 PM
    Monday, February 24, 2014 10:32 PM
    Moderator
  • Type conversion in expression (CONVERT(varchar(10),[p2].[PART],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression ([e].[STATUS]=CONVERT(varchar(10),[p2].[PART],0)) may affect "SeekPlan" in query plan choice
    Monday, February 24, 2014 10:40 PM
  • Hello,


    Is your query performing slowly? What I mean is that is just a warning. Is there an index on that column that the plan is not using?

    The following is a post related to this issue:

    http://sqlblog.com/blogs/rob_farley/archive/2013/09/23/string-length-and-sargability.aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, February 24, 2014 10:44 PM
    Moderator
  • This is very much expected behavior if you have different datatypes to join together.

    The best thing is to change your table structure. Ideally it should be the same data types and I dont think to have two different datatypes for the similar data as you are doing a joining between tables.

    If at all not possible, Then if the table is small, then you can first filer the data (note, you need to filter with condition) and push to a temp table where the temp table would have varchar datatype.Later, you can use the temp table to join the actual table. This would resolve your issue.

    Again, I would go with changing the datatype if possible.

    eg:

    --First filter and insert into temp table as below

     Insert into #temp_Table

    Select * from PART_LIST where PART_TYPE = 'ABC' --This would filter and move the data to temp table

    --Use the temp table for join purpose

    LEFT OUTER  JOIN #temp_Table p2 ON e.status  = p2.PART

    Tuesday, February 25, 2014 4:57 AM
  • ON cast(e.status as nvarchar(100))  = p2.PART

    or 

    ON  e.status = cast(p2.PART as part (10))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 25, 2014 8:25 AM
    Answerer
  • Tried both the methods. Still the same error.
    Tuesday, February 25, 2014 12:03 PM
  • If you tried with temp table solution, Can you show me the table structure for Temp table? It is assumed that you have varchar datatype assigned for PART column.
    Tuesday, February 25, 2014 12:10 PM
  • Hello,

    Someone have the same issue with you, please take a look at the feedback and vote it:
    https://connect.microsoft.com/SQLServer/feedback/details/695556/new-type-conversion-in-expression-warning-in-sql2012-to-noisy-to-practical-use

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, March 5, 2014 1:47 AM
    Moderator
  • you have data in PART_LIST.part column that is more than 10 characters in length.
    Wednesday, March 5, 2014 2:02 AM