1

A few days ago I had a question about warnings in the execution plan

Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

and that was solved, thanks Paul. Now I have another question regarding the cardinality estimate, this time it is caused by the bit data type.

I am using sys.data_spaces and sys.indexes

to run a simple query to get some information about my indexes:

SELECT i.name AS index_name  
,[ObjectType] =             
  i.type_desc + 
  CAST ( CASE WHEN CAST (i.is_primary_key AS INT)  = 1 
  THEN SPACE(2)  + '     Primary Key '      ELSE '' END AS NVARCHAR(30)) + 

  CAST ( CASE WHEN i.is_unique_constraint = 1 THEN SPACE(2)  + 
  'Unique Constraint' ELSE '' END AS NVARCHAR(30))+

  CAST ( CASE WHEN i.is_unique = 1 THEN 
         CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 
              THEN SPACE(2)  + 'Unique' ELSE '' END 
         ELSE SPACE(2)  + 'Non-Unique' END AS NVARCHAR(30)) +

  CAST ( CASE WHEN i.has_filter = 1 THEN SPACE(2)  + 'Filtered' 
         ELSE '' END AS NVARCHAR(30)) +

  CAST ( CASE WHEN i.is_padded = 1 THEN SPACE(2)  + 'Padded'            
         ELSE '' END AS NVARCHAR(30))

    --,i.type_desc  
    --,is_unique  
    --,ds.type_desc AS filegroup_or_partition_scheme  
    ,ds.name AS filegroup_or_partition_scheme_name  
    ,ignore_dup_key  
    --,is_primary_key  
    ,is_unique_constraint  
    ,fill_factor  
    ,is_padded  
    --,is_disabled  
    ,allow_row_locks  
    ,allow_page_locks  
FROM sys.indexes AS i  
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id  
WHERE 1=1
  AND is_hypothetical = 0 
  AND i.is_disabled = 0
  AND ds.is_system = 0  

GO 

the query runs fine enter image description here

but somehow dealing with the bit columns is upsetting the execution plan, causing some warnings related to conversion:

--Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(32),0)=(1) THEN ' Primary Key ' ELSE '' END,0)) may affect "CardinalityEstimate" in query plan choice,

--Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(64),0)=(1) THEN ' Unique Constraint' ELSE '' END,0)) may affect "CardinalityEstimate" in query plan choice,

--Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(131072),0)=(1) THEN ' Filtered' ELSE '' END,0)) may affect "CardinalityEstimate" in query plan choice,

--Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(16),0)=(1) THEN ' Padded' ELSE '' END,0)) may affect "CardinalityEstimate" in query plan choice

My question(s) are:

I am dealing only with the query returning columns, should I worry about this warning?

is it related to the bit data type?

is there any way to change the code and get rid of this warning?

if it is only noise, can I regulate it?

  • 1
    I would ignore this, especially since it's being thrown by system views. Unfortunately there's no way to mute it. – Erik Darling Apr 24 '18 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.