Implicit Conversion: The .NET One

This lesson covers a specific, extremely common problem in ASP.NET applications: the C# looks correct, the SQL looks correct, the index exists — and every query scans the table anyway.

The mechanism

SQL Server has a fixed data type precedence order. When comparing two values of different types, the lower-precedence one is converted to the higher. NVARCHAR has higher precedence than VARCHAR.

So if a column is VARCHAR(20) and the parameter is NVARCHAR(20), SQL Server cannot convert the parameter down. It converts the column up — every row of it — and then compares. Converting the column means the predicate is now a function on the column, which means it is not SARGable, which means a scan.

The plan calls this CONVERT_IMPLICIT and, on the operator, shows the warning: "Type conversion in expression may affect CardinalityEstimate in query plan choice."

Why .NET produces it by default

Microsoft.Data.SqlClient (and the older System.Data.SqlClient) maps System.String to NVARCHAR unless told otherwise. That is a sensible default — .NET strings are Unicode. It is also why an application against a VARCHAR schema scans on every string lookup.

// This sends @CustomerCode as NVARCHAR(4000).
// If dbo.Customer.CustomerCode is VARCHAR(20), every execution scans the table.
cmd.Parameters.AddWithValue("@CustomerCode", code);

AddWithValue compounds it by also inferring the length from the value, so "ABC" becomes NVARCHAR(3) and "ABCD" becomes NVARCHAR(4) — different parameter signatures, different cache entries, plan cache bloat on top of the scan.

Entity Framework Core has the same default: a string property maps to nvarchar(max) unless the model says otherwise, and a LINQ comparison against a varchar column produces the same conversion.

Spotting it in a plan

Look for:

The yellow warning triangle on a Scan or Seek operator with the CardinalityEstimate message.
CONVERT_IMPLICIT(nvarchar(...), [Column], 0) in the operator's Predicate property.
A Clustered Index Scan where you expected a seek and an index clearly exists on the column.

Finding it across the whole application at once

This sweep of the plan cache is the highest-value single query in the module. It finds every cached plan containing a conversion that affected plan choice:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;   -- reading cached plan XML, not user data

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
       stmt.value('(@StatementText)[1]', 'nvarchar(max)') AS statement_text,
       t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]','sysname') AS schema_name,
       t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','sysname')  AS table_name,
       t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','sysname') AS column_name,
       stmt.value('(@StatementSubTreeCost)[1]','float')   AS subtree_cost,
       qs.execution_count,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000 AS avg_ms
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS s(stmt)
CROSS APPLY stmt.nodes('.//ScalarOperator[@ScalarString[contains(.,"CONVERT_IMPLICIT")]]') AS c(t)
LEFT JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE stmt.exist('@StatementText[contains(.,"SELECT")]') = 1
ORDER BY qs.total_elapsed_time DESC;

The XML parsing makes this heavy on a large plan cache — run it during a quiet period. The simpler string match is cheaper and catches most of them:

SELECT TOP (25)
       SUBSTRING(t.text, 1, 400) AS query_snippet,
       qs.execution_count,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000 AS avg_ms,
       p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE CAST(p.query_plan AS nvarchar(max)) LIKE '%PlanAffectingConvert%'
ORDER BY qs.total_elapsed_time DESC;

Finding the schema-side risk directly

You can also find candidate columns before anything is slow — every VARCHAR/CHAR column in the database that an application might compare a .NET string against:

SELECT  OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
        OBJECT_NAME(c.object_id)        AS table_name,
        c.name                          AS column_name,
        ty.name                         AS data_type,
        c.max_length,
        (SELECT SUM(p.rows) FROM sys.partitions p
          WHERE p.object_id = c.object_id AND p.index_id IN (0,1)) AS approx_rows
FROM sys.columns AS c
JOIN sys.types   AS ty ON ty.user_type_id = c.user_type_id
WHERE ty.name IN ('varchar','char')
  AND OBJECTPROPERTY(c.object_id,'IsUserTable') = 1
  AND EXISTS (SELECT 1 FROM sys.index_columns ic
               WHERE ic.object_id = c.object_id AND ic.column_id = c.column_id)
ORDER BY approx_rows DESC;

Indexed varchar columns on large tables are exactly where this bites.

Reproducing it

DROP TABLE IF EXISTS #Customer;
CREATE TABLE #Customer (
    CustomerId   int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerCode varchar(20)  NOT NULL,      -- VARCHAR, as legacy schemas often are
    CustomerName nvarchar(200) NOT NULL
);

INSERT INTO #Customer (CustomerCode, CustomerName)
SELECT TOP (100000)
       'C' + RIGHT('0000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(10)), 7),
       N'Customer ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS nvarchar(10))
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON #Customer (CustomerCode) INCLUDE (CustomerName);

SET STATISTICS IO ON;

-- What .NET sends by default: NVARCHAR parameter -> converts the COLUMN -> scan
DECLARE @codeN nvarchar(20) = N'C0004242';
SELECT CustomerId, CustomerName FROM #Customer WHERE CustomerCode = @codeN;

-- Matching type -> seek
DECLARE @codeV varchar(20) = 'C0004242';
SELECT CustomerId, CustomerName FROM #Customer WHERE CustomerCode = @codeV;

SET STATISTICS IO OFF;

Run both with the actual plan on. The first shows a scan with the conversion warning; the second is a one-row seek. On 100,000 rows the difference is already large; on a fact table it is the whole report.

The fixes, in order of preference

1. Fix the .NET parameter type. No schema change, no downtime, one line.

// ADO.NET: state the type and the length explicitly
var p = cmd.Parameters.Add("@CustomerCode", SqlDbType.VarChar, 20);
p.Value = code;

// Dapper
conn.Query<Customer>(sql, new { CustomerCode = new DbString {
    Value = code, IsAnsi = true, IsFixedLength = false, Length = 20 } });

The Dapper IsAnsi = true flag is the direct expression of "send this as VARCHAR, not NVARCHAR."

2. Fix the EF Core mapping. IsUnicode(false) makes EF send varchar:

modelBuilder.Entity<Customer>()
    .Property(c => c.CustomerCode)
    .HasColumnType("varchar(20)")   // or .IsUnicode(false).HasMaxLength(20)
    .IsUnicode(false)
    .HasMaxLength(20);

Setting HasMaxLength matters as well as the type: without it EF may send varchar(max), which avoids the Unicode conversion but can still cause plan issues.

3. Align the column type. If the column should have been NVARCHAR all along, change it — but this rewrites the table and every index on it, needs a maintenance window, and doubles the storage for that column. It is the right call when the data genuinely needs Unicode, not merely to fix a plan.

4. Cast in the SQL — WHERE CustomerCode = CAST(@code AS varchar(20)). Works, and it is the right choice when you control the SQL but not the caller. Note the direction: cast the parameter, never the column.

The same issue between columns

Implicit conversion also appears in JOIN conditions when two tables disagree on type — a varchar key joined to an nvarchar key, or an int joined to a bigint. The join column gets converted and loses its seek, which turns an efficient nested-loops join into a scan-and-hash.

-- Find join-key type mismatches across foreign key relationships
SELECT  OBJECT_NAME(fk.parent_object_id)     AS child_table,
        pc.name                              AS child_column,
        pt.name + '(' + CAST(pc.max_length AS varchar(10)) + ')'  AS child_type,
        OBJECT_NAME(fk.referenced_object_id) AS parent_table,
        rc.name                              AS parent_column,
        rt.name + '(' + CAST(rc.max_length AS varchar(10)) + ')'  AS parent_type
FROM sys.foreign_key_columns AS fkc
JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS pc ON pc.object_id = fkc.parent_object_id     AND pc.column_id = fkc.parent_column_id
JOIN sys.columns AS rc ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id
JOIN sys.types   AS pt ON pt.user_type_id = pc.user_type_id
JOIN sys.types   AS rt ON rt.user_type_id = rc.user_type_id
WHERE pt.name <> rt.name OR pc.max_length <> rc.max_length;

In practice

SqlClient maps System.String to NVARCHAR by default, and NVARCHAR has higher type precedence than VARCHAR — so comparing to a VARCHAR column converts the column, not the parameter, and that kills the seek. The C# looks fine and the index exists, so it is easy to miss. The plan shows CONVERT_IMPLICIT and a cardinality warning. Fix it on the client side with an explicit SqlDbType.VarChar parameter or IsUnicode(false) in the EF mapping, then sweep the plan cache for PlanAffectingConvert to find every other query with the same problem.