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
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?
