Type Conversion may affect cardinality estimate in query plan

Type Conversion may affect cardinality estimate in query plan is a warning found on Execution Plans. This may negatively impact the correct index usage. Cardinality is a critical part of how indexes are used.

Data type matching has many benefits. It’s a practise often neglected in database design but which can have a number of long term consequences for  query access and database performance

One long term cost of neglecting data type matching is it can lead to more memory required. When data types are matched , this leads to less bytes requires , leading to less data pages in the SQL Server buffer cache . leading to faster query performance

Another way to look at it is to say if datatype usage is inefficient there will be more data pages in memory which will constrict the performance of other queries – as they will not have enough space

One of the potential impacts of relying on an implicit conversion  is the use of an index scan versus the index seek. This occurs because the column values must first be converted before they can be compared.  An index seek is a more efficient mehod to access the data in comparison to an index scan.You can check for index scans , by either reading the execution plan or checking for the SQL Server profiler Scan Event

When performance tuning how can you discover queries exhibiting these warning messages. One quick way is to scan the plan cache for plans with these warnings

Read More on Type Conversion may affect cardinality estimate in query plan 

Implicit conversion of data types in the query engine (SQL Server DBA)

Why does Query Optimizer choose a Clustered Index Scan?

Related articles

Find Table Scans in Query Plan Cache

Memory grants and execution plans