Page History
...
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 > >
...
...