Cardinality Estimate warning when a function(IS_MEMBER ) is used in where clause in SQLServer

Question

Consider the following Scenarios

Test data

CREATE TABLE   T1 (
     COL1   numeric (6, 0) NOT NULL,
     COL2   numeric (18, 0) NOT NULL,
     COL3   numeric (18, 0) NOT NULL,
     COL4   numeric (5, 0) NOT NULL,
     COL5   numeric (18, 0) NOT NULL,
     COL6   varchar (20) NOT NULL,
     COL7   varchar (50) NULL,
     COL8   numeric (1, 0) NULL,
     COL9   numeric (18, 0) NULL,
     COL10   varchar (20) NULL 
 ) 

ALTER TABLE  T1    
ADD PRIMARY KEY ( COL1,COL2, COL3,  COL4,  COL5, COL6    )

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_1',    '000002',   0,  NULL,   'admin_group')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_2',    '000002',   0,  NULL,   'admin_group')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_3',    '000002',   0,  NULL,   'QABrowns')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_4',    '000002',   0,  NULL,   'QABrowns')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_5',    '000002',   0,  NULL,   'QABrowns')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_6',    '000002',   0,  NULL,   'QABrowns')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_7',    '000002',   0,  NULL,   'QABrowns')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_8',    '000002',   0,  NULL,   'SuperUser')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_9',    '000002',   0,  NULL,   'SuperUser')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_10',   '000002',   0,  NULL,   'SuperUser')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_11',   '000002',   0,  NULL,   'LOLCOP2')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_12',   '000002',   0,  NULL,   'LOLCOP2')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_13',   '000002',   0,  NULL,   'LOLCOP2')

INSERT INTO  T1 VALUES (1,  2,  12, 1,  11, 'COL_14',   '000002',   0,  NULL,   'LOLCOP2')

Scenario 1:

 SELECT T1.* from  T1 
    WHERE IS_MEMBER( COL10)=1 

--Execution Plan warning--

"Type conversion in expression (CONVERT_IMPLICIT(nvarchar(20),[V9WEBDB1].[V9WEBDB1].[T3].[COL10],0)) may affect "CardinalityEstimate" in query plan choice"

Scenario 2

SELECT * INTO  T3  FROM  T1 WHERE COL10 IN ('admin_group')
ALTER TABLE  T3    
ADD PRIMARY KEY ( COL1,COL2, COL3,  COL4,  COL5, COL6    )
UPDATE STATISTICS  T3   
INSERT INTO  T3 SELECT * FROM  T1 WHERE COL10 IN ('LOLCOP2','QABrowns','SuperUser')

UPDATE STATISTICS  T3

SELECT T3.* from  T3 
WHERE IS_MEMBER( COL10)=1 

--Execution Plan warnings :

"Type conversion in expression (CONVERT_IMPLICIT(nvarchar(20),[V9WEBDB1].[V9WEBDB1].[T3].[COL10],0)) may affect "CardinalityEstimate" in query plan choice"

Scenario 3

SELECT * INTO  T4  FROM  T1 WHERE COL10 IN ('admin_group')
ALTER TABLE  T4    
ADD PRIMARY KEY ( COL1,COL2, COL3,  COL4,  COL5, COL6    )

UPDATE STATISTICS  T4   

SELECT T4.* from  T4 
WHERE IS_MEMBER( COL10)=1 

Execution Plan warnings--None

INSERT INTO  T4 SELECT * FROM  T1 WHERE COL10 IN ('LOLCOP2','QABrowns','SuperUser')

UPDATE STATISTICS  T4 

SELECT T4.* from  T4 
WHERE IS_MEMBER( COL10)=1 

Execution Plan warnings--None

ASK:

In 1st and 2nd scenarios

SELECT T1.* from  T1 WHERE IS_MEMBER( COL10)=1 

sql gives bellow warning

"Type conversion in expression (CONVERT_IMPLICIT(nvarchar(20),[V9WEBDB1].[V9WEBDB1].[T3].[COL10],0)) may affect "CardinalityEstimate" in query plan choice"

But in 3rd scenario

SELECT T1.* from  T1 WHERE IS_MEMBER( COL10)=1 

sql does not gives any warnings.

Data type of COL10 is varchar(20).

Server Collation : SQL_Latin1_General_CP1_CI_AS

1) Why does this warning happens?
2) How to avoid above warning?

Answer 1

1) Why does this warning happens?

Your table column col10 is varchar(20) but IS_MEMBER() accepts sysname as a parameter, so the server converts your varchar(20) to nvarchar(20) (sysname is nvarchar(128))

2) How to avoid above warning?

Change your col10 type to sysname or at least to nvarchar(20)