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?

Paul White
  • 94,921
  • 30
  • 437
  • 687
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

0 Answers0