Implicit conversions

In one line: when a query compares two different data types, SQL Server converts one of them. If it converts the value, nothing happens to performance. If it converts the column, it may cost an index scan, and for the classic varchar/nvarchar case the column’s collation decides.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/implicit-conversion on GitHub.

What you’ll see

  • A one-row lookup that reads thousands of pages. In the demo, 2,713 reads instead of 9, a 301× difference.
  • CONVERT_IMPLICIT in the plan, and sometimes a yellow warning triangle: PlanAffectingConvert.
  • A report join between two tables that hold the same key in different types, scanning instead of seeking.
  • Occasionally an outright error: Msg 245: Conversion failed when converting the varchar value ... to data type int.

Why it happens

The type with lower data type precedence gets converted. When a varchar column meets an nvarchar value (which is what .NET’s AddWithValue sends for any string), the column loses.

Under a Windows collation, the optimizer can still seek through the conversion, because both types sort the same way. Under a SQL collation (such as SQL_Latin1_General_CP1_CI_AS) they sort differently, no seekable range exists, and every row is converted and compared.

What to do

  • Match the types:
    • in the driver: set the parameter type explicitly instead of letting AddWithValue infer it
    • in procedure parameters
    • between columns that join to each other
  • Or make the column nvarchar. With an nvarchar column and a varchar value, the value is converted once and the index works normally.
  • Act on ConvertIssue="Seek Plan". That warning means a conversion prevented a seek.

What doesn’t help

  • Treating every CONVERT_IMPLICIT as a problem. Of twelve mismatched comparisons in the demo, eight still seeked. int/bigint, int/decimal and date/datetime all seeked, with accurate estimates.
  • Chasing the “Cardinality Estimate” warning. It fires even for a CONVERT in the SELECT list, which can’t affect anything. Meanwhile a converted column under a Windows collation raises no warning at all.
  • Comparing a varchar column to an int value. That’s a bug, not a slowdown: every row is converted to int, and the first non-numeric value fails the query.

Check your server

-- Cached plans in this database where a conversion prevented a seek.
-- Shredding plan XML is expensive on a large cache: run it off-peak.
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
        st.text,
        Expression = w.n.value('@Expression', 'nvarchar(400)'),
        qs.execution_count,
        AvgReads   = qs.total_logical_reads / NULLIF(qs.execution_count, 0)
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//PlanAffectingConvert[@ConvertIssue="Seek Plan"]') AS w(n)
CROSS APPLY (SELECT dbid = CAST(pa.value AS int)
             FROM   sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
             WHERE  pa.attribute = 'dbid') AS pdb
WHERE   pdb.dbid = DB_ID()
ORDER BY qs.total_logical_reads DESC;

The query uses the plan’s dbid attribute because sys.dm_exec_sql_text.dbid is NULL for ad hoc and sp_executesql statements, which covers almost everything an application sends.

See it happen

The implicit-conversion demo runs fifteen typed queries against the same customer code stored three ways. It records the operator, the estimate, the reads and the warnings for each, and the query above finds exactly its three scans and nothing else.

Related: non-SARGable predicates · data type traps · key lookups and the tipping point

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights