How to fix Cardinality Estimate warning for Variant column
CREATE TABLE [dbo].[Variant]( [Id] [int] IDENTITY(1,1) NOT NULL, [DataType] [nchar](10) NULL, [Value] [nchar](10) NULL ) CREATE NONCLUSTERED INDEX [Variant_DataType] ON [dbo].[Variant] ( [DataType] ASC ) INCLUDE ( [Value])
The type of the data stored in Value column is determined by DataType field. I want to perform numeric operations only on the rows with INT DataType, which works fine, but, I get the following cardinality estimate warning in the query plan.
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(int,[Faisal].[dbo].[Variant].[Value],0)" />
Here is my query, I have also attached the pan.
select sum(CONVERT(INT, Value)) from Variant WHERE DataType = 'INT'
How can I restructure the query to fix the warning. I am working with a legacy database and don't want to make any schema changes, is it possible to fix this by rewriting the query.
Thanks.
Here is the sample data I am using for testing.
INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (1, N'INT ', N'1 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (2, N'STRING ', N'two ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (3, N'INT ', N'3 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (4, N'STRING ', N'four ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (5, N'INT ', N'5 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (6, N'STRING ', N'six ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (7, N'INT ', N'7 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (8, N'STRING ', N'eight ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (9, N'INT ', N'9 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (10, N'STRING ', N'ten ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (11, N'INT ', N'11 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (12, N'STRING ', N'twelve ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (13, N'INT ', N'13 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (14, N'STRING ', N'fourteen ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (15, N'INT ', N'15 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (16, N'STRING ', N'sixteen ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (17, N'INT ', N'17 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (18, N'STRING ', N'eighteen ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (19, N'INT ', N'19 ') INSERT [dbo].[Variant] ([Id], [DataType], [Value]) VALUES (20, N'STRING ', N'twenty ')
Just saw this post, about ConvertIssue="Cardinality Estimate" warning being too noisy. Since its so common, practically, how much can it affect query plan?
That said, this is a problematic (and non-relational) design pattern, so you should expect problems in the future. You may be able to work around some of these with filtered indexes and indexed views, but the fundamentals are unsound.