The Implicit Conversion That Quietly Kills Your Index

An implicit conversion lets SQL Server compare values with different data types. When that conversion affects an indexed column, a simple lookup can become harder to optimize.

The Engine Has to Choose a Type

A comparison needs compatible values. SQL Server follows data type precedence rules when an expression combines different types. In many comparisons, the lower-precedence operand converts to the higher-precedence type.

SELECT SQL_VARIANT_PROPERTY(1 + CAST(2 AS decimal(10,2)),
                            'BaseType') AS result_type;
SELECT TRY_CONVERT(int, N'123') AS valid_number,
       TRY_CONVERT(int, N'ABC') AS invalid_number;

That rule explains correctness as well as performance. Comparing a text identifier with an integer can request a numeric conversion. If the text contains something other than a valid number, the statement can fail.

Do not decide that a column is numeric because the first few values contain digits. Account codes can include letters or meaningful leading zeros. Choose the type from the business meaning, then keep the surrounding interfaces consistent.

Build a Small Comparison

Use this temporary table in one query window. The explicit SQL collation makes the example’s comparison rules visible. The generated data is only a demonstration, not evidence of a measured production result.

CREATE TABLE #ConversionDemo
(
    Code varchar(12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    Detail varchar(40) NOT NULL,
    CONSTRAINT PK_ConversionDemo PRIMARY KEY (Code)
);
WITH Digits AS
(
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
)
INSERT #ConversionDemo(Code, Detail)
SELECT RIGHT('0000' + CONVERT(varchar(4),
       a.n + 10*b.n + 100*c.n + 1000*d.n), 4), 'Sample'
FROM Digits AS a CROSS JOIN Digits AS b
CROSS JOIN Digits AS c CROSS JOIN Digits AS d;

Enable the actual execution plan before running the next comparisons. Keep the data, selected columns, and search value unchanged. The parameter type is the difference you want to investigate.

Compare Matching and Mismatched Parameters

The first parameter matches the column’s varchar definition. The second uses nvarchar, which has higher precedence than varchar. That can introduce conversion work on the column side of the comparison.

EXEC sys.sp_executesql
    N'SELECT Code, Detail FROM #ConversionDemo WHERE Code = @Code;',
    N'@Code varchar(12)', @Code = '0042';
EXEC sys.sp_executesql
    N'SELECT Code, Detail FROM #ConversionDemo WHERE Code = @Code;',
    N'@Code nvarchar(12)', @Code = N'0042';

Do not promise that every mismatch produces a scan. Collation, supported transformations, estimates, and cost all influence the chosen plan. Some conversions still permit a useful seek.

A tiny table can also make a scan perfectly reasonable. The purpose of this example is to inspect the expression and access path. Your real workload needs its own measurements.

Read More Than the Warning Icon

Open the operator properties and inspect the seek predicates and residual predicates. Look for CONVERT_IMPLICIT around the column or parameter. A warning can point to an estimation or access-path concern, but it does not quantify the damage.

Compare estimated rows, actual rows, and rows read where available. Use STATISTICS IO and TIME during a controlled test when you need measured costs. Save both plans so the comparison does not depend on memory.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC sys.sp_executesql
    N'SELECT Code, Detail FROM #ConversionDemo WHERE Code = @Code;',
    N'@Code varchar(12)', @Code = '0042';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

A conversion appearing only in the projected result may be harmless for index access. Focus on where the conversion occurs. Searching every plan for the word conversion produces plenty of noise.

Fix the Interface Before Casting the Column

The durable fix is often in application parameter binding. Match the intended SQL type, length, precision, and scale to the schema. Also check stored procedure parameters and intermediate tables.

Wrapping the indexed column in an explicit CAST can preserve the same access problem. Converting an incoming value may be better, but only when that conversion preserves meaning. Validate truncation, invalid characters, and numeric ranges.

Do not change every text column to nvarchar merely to hide one mismatched parameter. That is a schema decision with storage and compatibility consequences. Correct the specific contract that produced the mismatch.

Retest the Real Call

Repeat the comparison through the application after changing its parameter definition. A literal pasted into SSMS might use a different type from the driver. Capture the actual parameter metadata when the difference remains unexplained.

Review representative values, including uncommon ones and boundary lengths. Confirm the returned data before celebrating a different plan. A fast lookup that silently changes the identifier is still the wrong lookup.

Type matching is not cosmetic tidiness, it is part of the query contract.

This post was rewritten from scratch in September 2026. The original, published on 2009-10-10, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.