Short answer: when an application sends a string parameter as nvarchar and the column is varchar, SQL Server converts the column, because nvarchar has higher data type precedence. With a SQL collation such as SQL_Latin1_General_CP1_CI_AS, the index on that column can no longer be sought. In my test, one lookup went from 6 logical reads to 4,484. Fix it in the data access layer by sending varchar for that parameter.
This is a common cause of unexplained CPU on SQL Server behind .NET and Java applications. The query text looks fine, the index exists, and the plan shows an index operator, just a scan instead of a seek. I reproduced it on SQL Server 2022 (CU27, Developer Edition in Docker) with three 1,000,000-row tables that differ only in column type and collation. The scripts and full output are in the lab folder.
The reproduction
The table has an email varchar(100) column with a nonclustered index, and the server default collation SQL_Latin1_General_CP1_CI_AS. This is the shape of statement a driver sends when it passes a string as Unicode, which is the default in ADO.NET and the Microsoft JDBC driver (the declared length varies by driver):
EXEC sp_executesql
N'SELECT id, plan_code FROM dbo.accounts_sqlcoll WHERE email = @e',
N'@e nvarchar(4000)', @e = N'user777777@example.com';The actual plan and I/O:
Table 'accounts_sqlcoll'. Scan count 1, logical reads 4484
|--Index Scan(OBJECT:([convlab].[dbo].[accounts_sqlcoll].[ix_accounts_sqlcoll_email]),
WHERE:(CONVERT_IMPLICIT(nvarchar(100),[convlab].[dbo].[accounts_sqlcoll].[email],0)=[@e]))The same statement with @e varchar(100):
Table 'accounts_sqlcoll'. Scan count 1, logical reads 6
|--Index Seek(OBJECT:([convlab].[dbo].[accounts_sqlcoll].[ix_accounts_sqlcoll_email]),
SEEK:([convlab].[dbo].[accounts_sqlcoll].[email]=[@e]) ORDERED FORWARD)The index is still used in the bad plan, which is why this gets missed. It is read from end to end, and every one of the 1,000,000 values is converted to nvarchar before the comparison. Cost grows with the table, not with the result.
Over 300 executions each, with different emails, Query Store recorded:
| Parameter → column | Avg logical reads | Avg CPU |
|---|---|---|
nvarchar(4000) → varchar, SQL collation | 4,484 | 86.7 ms |
nvarchar(4000) → varchar, Windows collation | 6 | 0.13 ms |
varchar(100) → varchar, SQL collation | 6 | 0.11 ms |
The container ran x86-64 SQL Server under emulation on an ARM laptop, so the CPU times are inflated. Compare the logical reads and the ratio, not the milliseconds.
Collation decides the outcome
Precedence alone does not cause the scan. What matters is whether SQL Server can turn the converted comparison into a seek range. With a Windows collation (Latin1_General_CI_AS), it can. The plan still shows CONVERT_IMPLICIT, but it computes a range with GetRangeThroughConvert and seeks:
|--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert(
CONVERT_IMPLICIT(nvarchar(4000),[@e],0),CONVERT_IMPLICIT(nvarchar(4000),[@e],0),(62))))
|--Index Seek(... SEEK:([email] > [Expr1005] AND [email] < [Expr1006]),
WHERE:(CONVERT_IMPLICIT(nvarchar(100),[email],0)=CONVERT_IMPLICIT(nvarchar(4000),[@e],0)))With a SQL collation (SQL_Latin1_General_CP1_CI_AS), varchar and nvarchar values sort by different rules, so no such range exists and SQL Server scans. That collation is the default server collation for installations with US English locale settings. Many databases created years ago, and the databases restored from them, still use it. Check yours:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS database_collation;
SELECT t.name AS table_name, c.name AS column_name,
TYPE_NAME(c.user_type_id) AS data_type, c.max_length, c.collation_name
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE TYPE_NAME(c.user_type_id) IN ('varchar', 'char')
AND c.collation_name LIKE 'SQL[_]%'
ORDER BY t.name, c.name;The reverse case is harmless. With an nvarchar column and a varchar parameter, the lab converted the parameter, not the column, and sought in 6 reads. Only varchar or char columns that receive Unicode parameters are at risk.
Find affected queries in Query Store
The optimizer records the problem as a PlanAffectingConvert warning in the plan XML. This query finds plans with that warning and ranks them by reads. It ran against the lab database and returned exactly the bad query:
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
plans AS (
SELECT p.plan_id, q.query_id, qt.query_sql_text, TRY_CONVERT(xml, p.query_plan) AS plan_xml
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
),
warnings AS (
SELECT pl.query_id, pl.plan_id, pl.query_sql_text,
w.n.value('@ConvertIssue', 'varchar(40)') AS convert_issue,
w.n.value('@Expression', 'nvarchar(400)') AS expression
FROM plans pl
CROSS APPLY pl.plan_xml.nodes('//Warnings/PlanAffectingConvert') AS w(n)
)
SELECT w.query_id, w.plan_id, LEFT(w.query_sql_text, 90) AS query_text,
w.convert_issue, LEFT(w.expression, 90) AS expression,
SUM(rs.count_executions) AS execs,
CAST(MAX(rs.avg_logical_io_reads) AS bigint) AS avg_logical_reads
FROM warnings w
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = w.plan_id
GROUP BY w.query_id, w.plan_id, w.query_sql_text, w.convert_issue, w.expression
ORDER BY avg_logical_reads DESC;query_text | convert_issue | expression | execs | avg_logical_reads
(@e nvarchar(4000))SELECT id, plan_code FROM | Seek Plan | CONVERT_IMPLICIT(nvarchar(100),[convlab].[dbo].[accounts_sqlcoll].[email],0)=[@e] | 300 | 4484ConvertIssue = "Seek Plan"is the one that costs reads: the conversion prevented a seek."Cardinality Estimate"means the conversion may affect row estimates; it is worth a look but is often noise from conversions in the select list.- The Windows-collation query did not appear. Its conversion did not prevent a seek, so the optimizer raised no warning.
- Run it in a session with
QUOTED_IDENTIFIER ON. SSMS sets it by default; the ODBCsqlcmddoes not unless you pass-I. Without it, the XML methods fail with error 1934. - On a large Query Store, XML parsing is expensive. Restrict
plansto the top plans by reads or CPU over the last few days first, and run it on a readable secondary if you have one.
The parameter declaration at the start of query_sql_text, here (@e nvarchar(4000)), tells you the data access layer is responsible. The column's type is in the expression column.
Fix it in the driver or ORM
Send varchar with the column's length for the parameters that hit varchar columns. Change it per parameter where you can; a connection-wide switch changes every string you send.
| Stack | Default for a string | Fix |
|---|---|---|
ADO.NET (Microsoft.Data.SqlClient) | NVarChar, inferred from the .NET type | new SqlParameter("@e", SqlDbType.VarChar, 100) { Value = email }, or DbType = DbType.AnsiString. Avoid AddWithValue for these. |
| Dapper | Unicode | new DbString { Value = email, IsAnsi = true, Length = 100 } |
| EF Core | Strings mapped as Unicode (nvarchar) | Map the property to the real column: .IsUnicode(false).HasMaxLength(100) or [Unicode(false)]. EF then sends varchar parameters for it. |
| Microsoft JDBC driver (Hibernate, Spring) | Unicode: sendStringParametersAsUnicode=true | sendStringParametersAsUnicode=false in the connection string sends setString values as non-Unicode. Then use setNString for parameters that target nvarchar columns, because it always sends Unicode. |
Node.js mssql | NVarChar | request.input('e', sql.VarChar(100), email) |
Two cautions. First, a varchar parameter can only carry characters in the column's code page; see the next section. Second, the JDBC switch is global. If some columns are nvarchar and you send them non-Unicode strings, characters outside the code page are lost before they reach the server. Audit which columns are which before changing it, and prefer changing the column mappings in the ORM.
Changing the column to nvarchar also removes the mismatch, but it rewrites the table and its indexes, stores most text in two bytes per character (in the lab, 119 MB against 76 MB for the same rows), and needs a maintenance window on a large table. Changing the column to a Windows collation turns the scan into a seek but changes sort and comparison rules, and SQL Server refuses the change while an index, manually created statistics, a CHECK or FOREIGN KEY constraint, a computed column or a schema-bound object references the column, so those must be dropped and recreated around it. Both are schema projects, not quick fixes.
Why CAST in the query is a last resort
If you cannot change the driver, converting the parameter in the SQL does produce a seek:
WHERE email = CAST(@e AS varchar(100))
-- Index Seek ... SEEK:([email]=CONVERT(varchar(100),[@e],0)) logical reads 6The catch is that the conversion can change the value. In the lab, on the SQL_Latin1_General_CP1_CI_AS code page (1252):
SELECT CAST(N'zoë.ł@example.com' AS varchar(100)), -- zoë.l@example.com
CAST(N'用户@example.com' AS varchar(100)); -- ??@example.comë exists in code page 1252 and survives. ł does not and becomes l, a different, valid character. For a login or account lookup, that means a value the user typed can match another user's row. Characters with no near equivalent become ?. Validate or reject input outside the column's code page first, or keep the comparison in Unicode and fix the type mismatch in the schema.
Validate the fix
- Record the query's average logical reads, CPU and execution count from Query Store before the change.
- Deploy the parameter change. The new parameter declaration produces a new query text in Query Store, so compare the new
query_idwith the old one, not the same row over time. - Confirm the new plan shows an
Index Seekand noPlanAffectingConvertwarning. - Check total CPU on the instance. When a scan like this runs thousands of times a minute, fixing one query often moves the whole server.
To read a captured plan operator by operator, paste it into PlanReader's SQL Server analyzer. If the same query is fast for some values and slow for others, the cause is more likely parameter sniffing.
FAQ
What is CONVERT_IMPLICIT in a SQL Server execution plan?
It is a type conversion SQL Server added because two compared values have different data types. When an nvarchar parameter is compared with a varchar column, data type precedence converts the column to nvarchar. With a SQL collation, that conversion is applied to every row, so an index seek becomes a scan.
Why does my ORM send nvarchar parameters?
Most drivers map a string to Unicode by default. ADO.NET infers SqlDbType.NVarChar for a .NET string, the Microsoft JDBC driver sends strings as Unicode because sendStringParametersAsUnicode defaults to true, and EF Core maps strings as Unicode unless the property is configured with IsUnicode(false).
Does an nvarchar parameter always cause an index scan on a varchar column?
No. It depends on the column collation. In a SQL Server 2022 test, a varchar column with the SQL_Latin1_General_CP1_CI_AS collation was scanned (4,484 logical reads), while the same column with the Windows collation Latin1_General_CI_AS was still sought through GetRangeThroughConvert (6 logical reads). SQL_Latin1_General_CP1_CI_AS is the default server collation for US English installations.
How do I find queries affected by implicit conversion?
Search the plans in Query Store for the PlanAffectingConvert warning with ConvertIssue = "Seek Plan", and rank them by logical reads. The query in this article does that from sys.query_store_plan.
What is the safest fix for nvarchar to varchar implicit conversion?
Send the parameter as varchar with the column length, for that parameter only: SqlDbType.VarChar or DbType.AnsiString in ADO.NET, DbString with IsAnsi = true in Dapper, IsUnicode(false) in EF Core. Turning Unicode off for a whole JDBC connection also works but affects every string parameter, including ones meant for nvarchar columns.
Sources
- Data type precedence (Transact-SQL) —
nvarcharranks abovevarchar. - JDBC driver connection properties —
sendStringParametersAsUnicodeandsetNString. - ADO.NET: configuring parameters and parameter data types — type inference and
DbType.AnsiString. - EF Core: entity properties — Unicode configuration.
- Dapper README — "Ansi Strings and varchar".
- Lab: SQL Server implicit conversion — setup, plans, Query Store output.