By: Hadi Fadlallah | Updated: 2023-07-19 | Comments | Related: > Performance Tuning
Problem
Often SQL developers encounter a strange behavior of the query optimizer where an index scan is performed rather than an index seek when querying a column with a fixed value. This article explains how implicit conversions affect SQL query performance and cause the query optimizer to perform an index scan instead of index seek while searching for a specific value.
Solution
Implicit Conversion… A Quick Reminder
In SQL, implicit conversion is an automatic data type conversion done by the compiler within an operation where different data types are compared or integrated. For instance, if a user wants to insert an integer value within a VARCHAR column or even when a user tries to compare VARCHAR and NVARCHAR values.
SELECT * FROM Table WHERE 1 = '1'
Implicit conversion is not supported between all data types. Sometimes data types cannot be compared or may require some explicit functions. The diagram below summarizes the supported data type conversions in SQL Server.
Figure 1 - Implicit and explicit conversion (Reference: Microsoft Learn)
As the name implies, implicit conversion is a kind of invisible data processing to the end user, increasing resource consumption and query execution time. Implicit conversions could be acceptable when working with a small volume of data. In contrast, these operations become harmful while querying a large volume of data.
Index Scan vs. Index Seek
Index Scan and Index Seek are two SQL Server query execution plan operators that indicate that the query compiler decided to utilize an index (Clustered or Non-Clustered) during the execution.
- Index Scan: It indicates that the SQL engine will read the whole index looking for matches except if used with a TOP operator. The time of a scan operation is usually proportional to the size of the index.
Figure 2 - Index Scan (Reference: Stack Overflow)
- Index Seek: It indicates that the SQL engine will propagate the index to seek directly to matching records. The time taken depends on the number of matching records.
Figure 3 - Index Seek (Reference: Stack Overflow)
Usually, a seek operation is more performant than a scan operation unless scanning all values is required.
Figure 4 - Image Reference: Technowide.net
Implicit Conversion Causing Index Scan
One of the common implicit conversion effects in SQL Server is causing the query optimizer to perform an index scan instead of an index seek operation, even if the query is matching rows against a constant value.
SELECT * FROM TABLE WHERE [Column] = 'Some Value'
This mainly occurs when comparing some values to a VARCHAR column.
Querying an NVARCHAR Column
To illustrate this behavior, we ran the following experiment using the StackOverflow2010 database provided by Brent Ozar.
In the Users table, the DisplayName column type is NVARCHAR. We created another Column, encDisplayName, using the following SQL command:
ALTER TABLE dbo.Users ADD encDisplayName VARCHAR(40);
UPDATE dbo.Users SET encDisplayName = DisplayName;
Now, let's visualize the query execution plan while trying to filter rows using the DisplayName and encDisplayName columns.
Begin by filtering the DisplayName (NVARCHAR) column. In the first command, we tried to filter using a VARCHAR value. The query execution plan shows an index seek operation, meaning the implicit conversion did not affect the query execution.
SELECT *
FROM [StackOverflow2010].[dbo].[Users]
WHERE DisplayName = 'Hadi'
Figure 5 - Filtering using a VARCHAR value
The same execution plan is used when filtering using an NVARCHAR value.
SELECT *
FROM [StackOverflow2010].[dbo].[Users]
WHERE DisplayName = N'Hadi'
Figure 6 - Filtering using a NVARCHAR value
If we try to filter using an integer value, note that the query optimizer no longer chooses an index seek operation.
Figure 7 - Filtering using an integer value
Moreover, the following warning appears in the query execution plan.
Type conversion in expression (CONVERT_IMPLICIT(int,[StackOverflow2010].[dbo].[Users].[DisplayName],0)) may affect "CardinalityEstimate" in query plan choice. Type conversion in expression (CONVERT_IMPLICIT(int,[StackOverflow2010].[dbo].[Users].[DisplayName],0)) may affect "SeekPlan" in query plan choice…
This means that the query optimizer decided to convert the values of the DisplayName column to compare it with the integer value specified in the WHERE clause instead of converting the integer value (1) to NVARCHAR.
This problem can be solved by explicitly converting this value to NVARCHAR.
SELECT *
FROM [StackOverflow2010].[dbo].[Users]
WHERE DisplayName = CAST(1 as NVARCHAR(40))
Figure 8 - Explicitly converting the integer value to a string
Querying a VARCHAR Column
Now, let's repeat the same experiment over the VARCHAR column.
Filtering using a VARCHAR value results in an index seek operation.
SELECT *
FROM [StackOverflow2010].[dbo].[Users]
WHERE encDisplayName = 'Hadi'
While filtering using an NVARCHAR operation, the query optimizer uses an index scan operation. Since the query optimizer decided to convert all values in the encDisplayName column to NVARCHAR instead of just converting the NVARCHAR constant value to VARCHAR.
Figure 9 - Filtering using a VARCHAR value
This behavior may look strange, especially since we are comparing two string values. Still, the SQL engine considers converting an NVARCHAR value to VARCHAR unsafe and may lose some Unicode characters if the default collation does not support them. This is why it prefers converting the whole column rather than converting a single value.
As stated before, using an explicit conversion will solve this issue.
SELECT *
FROM [StackOverflow2010].[dbo].[Users]
WHERE encDisplayName = CAST(N'Hadi' as varchar(40))
Figure 10 - Using an explicit conversion to solve the issue
Conclusion
This article explained implicit conversions and the difference between an index scan and an index seek operation. It also illustrated how implicit conversion could affect the query plan estimation and let the query optimizer decide to use an index scan rather than an index seek. Finally, it explained how this behavior could be solved using explicit conversion.
Next Steps
- You can read more about implicit conversion performance issues in the following articles:
- Also, you can learn more about SQL data types:
- You can learn more about other conversion methods that exist in SQL Server:
About the author
Hadi Fadlallah is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com. He holds a Ph.D. in data science focusing on context-aware big data quality and two master's degrees in computer science and business computing. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-07-19
