Search code examples
sql-serverxpathxquery

Type conversion in expression affect CardinalityEstimate - data from XML

I have recently changed compatibility level from 100 to 130, now I have some issues with some of my queries.

Just for example:

declare @msg_data_xml xml;
declare @msg_data nvarchar(max) = '<ns1:SHPPLT xmlns:ns1="http://test.com/test"><ns1:MSG><ns1:GUID>BFE542A5-0761-AA07-E050-010A32017E4D</ns1:GUID></ns1:MSG><ns1:HEADER><ns1:SEAL_NUM>N01634959</ns1:SEAL_NUM></ns1:HEADER></ns1:SHPPLT>'
set @msg_data_xml = cast( @msg_data as xml)
    
;with   xmlnamespaces('http://test.com/test' as n)
select      
            COM_SEAL_NUM = h.hi.value('(n:SEAL_NUM)[1]', 'nvarchar(100)')   
from    @msg_data_xml.nodes('n:SHPPLT/n:HEADER') h(hi)

This gives me warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(100),[Expr1015],0)) may affect "CardinalityEstimate" in query plan choice;

Any idea what can I do to avoid this?

Solution

  • Either rewrite the query or use hint for forcing legacy cardinality estimation:

    OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));