Page History
...
The lack of attention to data type mismatches can have a significant negative impact on query performance. One case, in particular, is when the data type mismatch leads to an implicit conversion being performed on a column used in a search predicate. This implicit conversion prevents the use of an index seek and ensures that an index scan or table scan is performed to satisfy the query predicate. In this case, the implicit conversion is caused by the query optimizer by seeing the column with the implicit conversion as an expression rather than a column.
For example, see the following implicit conversion occurring in a search predicate and the result:
You would expect an index seek to occur on the ‘AK_Employee_NationalIDNumber’ index; however, the execution plan clearly shows that an index scan is performed instead of an index seek.
To determine the cause of having an index scan, mouse over the index scan operation of the execution plan to display the tooltip containing additional details:
Under the ‘Predicate’ section of the tooltip see that the ‘CONVERT_IMPLICIT(int,[AdventureWorks].[HumanResources].[Employee].[NationalIDNumber],0)=(253022876)’ implicit conversion is being performed on the ‘NationalIDNumber’ column. This column value is being converted because the data type of the column mismatch mismatches the data type of the literal value being used in the compare. To resolve the data mismatch, change the data type of the literal value to match the data type of the column:
The new execution plan for the updated TSQL is the following:
Note that the index scan is replaced by an index seek operation, optimizing successfully the query.
Use SQL Doctor to identify implicit conversions
| Newtablink | ||||
|---|---|---|---|---|
|
| Newtablink | ||||
|---|---|---|---|---|
|
You can click Show me the problem on the Recommendations window to display the TSQL.
SQL Doctor provides complete SQL performance tuning. Learn more > >
...
...