How to fix Cardinality Estimate warning for Variant column

Faisal 2015-09-23 19:26:25

Consider the following table.

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    ')
/storage/attachments/1368-variantplan.queryanalysis
Aaron Bertrand 2015-09-23 19:27:44
Could you consider a filtered index (e.g. WHERE DataType = N'INT')?
Faisal 2015-09-23 20:06:48
Yes, will need to create filters for multiple indexes, but, will give it a try.

Just saw this post, about ConvertIssue="Cardinality Estimate" warning being too noisy. Since its so common, practically, how much can it affect query plan?

https://connect.microsoft.com/SQLServer/feedback/details/695556/new-type-conversion-in-expression-warning-in-sql2012-to-noisy-to-practical-use

SQLkiwi 2015-09-26 11:39:26
The warning is noisy, and raised even when the type conversion cannot possibly affect a cardinality estimation, as the Connect item response notes. In this particular case, the estimate in the plan is exactly correct, so there is nothing to worry about.

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.

September 23, 2015
No Comments