SpDev Install free

Implicit conversion in SQL Server: why your index isn't used

An NVARCHAR parameter against a VARCHAR column can turn a 6-read seek into a 600-read scan. How to spot implicit conversions in the plan, and how to fix them in T-SQL and .NET.

By Areeb Ahmer, . 6 minute read.

A query filters on an indexed column, returns one row, and still reads the whole index. The plan shows an Index Scan where you expected a seek, and a yellow warning triangle on the SELECT operator. The cause is usually one character: an N in front of a string, or a parameter declared as NVARCHAR when the column is VARCHAR.

This is an implicit conversion. SQL Server can’t compare two different data types directly, so it converts one of them. When it has to convert the column, it can no longer use the index the way you meant.

The examples below run against the SpDevDemo database, on SQL Server 2022 with the default SQL_Latin1_General_CP1_CI_AS collation.

What it looks like

dbo.Orders has 200,000 rows and a unique index on OrderNumber, which is VARCHAR(20). Look up one order with an NVARCHAR variable:

SET STATISTICS IO ON;

DECLARE @Number NVARCHAR(20) = N'SO-0123456';
SELECT OrderId, Total FROM dbo.Orders WHERE OrderNumber = @Number;
Table 'Orders'. Scan count 1, logical reads 607

Now the same query with a VARCHAR variable:

DECLARE @Number VARCHAR(20) = 'SO-0123456';
SELECT OrderId, Total FROM dbo.Orders WHERE OrderNumber = @Number;

SET STATISTICS IO OFF;
Table 'Orders'. Scan count 0, logical reads 6

Same row, same index: 607 reads against 6. The first plan scans all 200,000 entries of IX_Orders_OrderNumber and converts every one of them before comparing. On a table with 50 million rows, that is the difference between a millisecond and several seconds, every time the query runs.

Why SQL Server converts the column

When two values of different types meet in a comparison, SQL Server converts the one with the lower data type precedence to the type with the higher one. The order, highest first, starts like this:

  1. datetimeoffset, datetime2, datetime, smalldatetime, date, time
  2. float, real, decimal, money, smallmoney
  3. bigint, int, smallint, tinyint, bit
  4. uniqueidentifier
  5. nvarchar, nchar, varchar, char
  6. varbinary, binary

nvarchar outranks varchar, so in OrderNumber = @Number the column loses. The predicate becomes:

CONVERT_IMPLICIT(nvarchar(20), [dbo].[Orders].[OrderNumber], 0) = [@Number]

An index on OrderNumber is sorted by the VARCHAR values under the column’s collation. A converted value isn’t something the index is sorted by, so SQL Server can’t seek to it. It reads everything and filters.

The direction matters. Compare an INT column with a string, and this time the string is converted, because int outranks varchar:

SET STATISTICS IO ON;
SELECT OrderId FROM dbo.Orders WHERE CustomerId = '42';
SET STATISTICS IO OFF;
Table 'Orders'. Scan count 1, logical reads 2

That one is harmless: the literal is converted once and the seek on IX_Orders_CustomerId still works. Turn it around, comparing a string column with a number, and you get the worst of both. Every row is converted, and the first value that isn’t a number stops the query:

SELECT OrderId FROM dbo.Orders WHERE OrderNumber = 123;
Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'SO-0000001' to data type int.

How to find it in a plan

An actual execution plan tells you in two places:

  • The root operator (SELECT, INSERT, UPDATE) has a warning: Type conversion in expression (CONVERT_IMPLICIT(…)) may affect “SeekPlan” in query plan choice. In the plan XML it is a PlanAffectingConvert element with ConvertIssue="Seek Plan".
  • The scan’s predicate contains CONVERT_IMPLICIT wrapped around the column, not around the parameter.

A second kind of warning, ConvertIssue="Cardinality Estimate", means the conversion hides the column’s statistics from the optimizer. That one doesn’t block a seek, but it can still produce a bad plan.

To find the queries on a server that already have the problem, search the plan cache. This needs VIEW SERVER STATE (on SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough):

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (20)
    DB_NAME(p.dbid)                             AS database_name,
    OBJECT_NAME(p.objectid, p.dbid)             AS module_name,
    c.value('@Expression', 'nvarchar(4000)')    AS conversion,
    qs.execution_count,
    qs.total_logical_reads
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('//PlanAffectingConvert[@ConvertIssue="Seek Plan"]') AS x(c)
WHERE p.dbid > 4          -- skip the system databases
ORDER BY qs.total_logical_reads DESC;

Sorted by total_logical_reads, the top rows are the conversions costing you the most. module_name is the procedure or function, and is empty for ad hoc queries. On the demo database, the 607-read lookup from the start of this article comes out on top.

The collation exception

The 607-read scan above uses a SQL collation, SQL_Latin1_General_CP1_CI_AS, which is the default for new installations in the US English locale. With a Windows collation such as Latin1_General_CI_AS, SQL Server can still seek. It converts the parameter into a range of VARCHAR values that could match (the plan shows GetRangeThroughConvert), seeks on that range, and applies the exact comparison to the few rows it finds.

CREATE TABLE dbo.Codes (
    Id   INT IDENTITY PRIMARY KEY,
    Code VARCHAR(20) COLLATE Latin1_General_CI_AS NOT NULL
);
INSERT dbo.Codes (Code) SELECT CONCAT('C', OrderId) FROM dbo.Orders;
CREATE INDEX IX_Codes_Code ON dbo.Codes (Code);

SET STATISTICS IO ON;
DECLARE @Code NVARCHAR(20) = N'C4242';
SELECT Id FROM dbo.Codes WHERE Code = @Code;
SET STATISTICS IO OFF;

DROP TABLE dbo.Codes;
Table 'Codes'. Scan count 1, logical reads 3

So the same mistake costs a scan on one server and almost nothing on another. Don’t rely on the collation to save you: the dynamic seek reads more rows than an exact one, its estimates are worse, and the next database you deploy to may use the other collation.

Where NVARCHAR parameters come from

You rarely type N'...' against a VARCHAR column on purpose. The usual sources:

  • ADO.NET AddWithValue. A .NET string becomes an NVARCHAR parameter, whatever the column type. We look at this in detail in the .NET article.
  • Dapper. Plain strings are sent as NVARCHAR(4000) unless you wrap them in DbString { IsAnsi = true }.
  • EF Core with a model that doesn’t say the column is non-Unicode. A database-first table with a VARCHAR column and a string property without IsUnicode(false) or HasColumnType("varchar(20)") gets NVARCHAR parameters.
  • JDBC. The Microsoft JDBC driver sends strings as Unicode by default (sendStringParametersAsUnicode=true).
  • Stored procedures whose parameters were declared NVARCHAR “to be safe”, against VARCHAR columns.
  • sp_executesql calls where the parameter list says @x NVARCHAR(50).

How to fix it

Make the types match. Which side to change depends on the data.

Change the parameter when the column’s type is right. In a procedure:

CREATE OR ALTER PROCEDURE dbo.GetOrderByNumber
    @OrderNumber VARCHAR(20)          -- was NVARCHAR(20)
AS
SELECT OrderId, CustomerId, Total
FROM dbo.Orders
WHERE OrderNumber = @OrderNumber;

A procedure parameter is converted once, at the call, which costs nothing. In .NET, give the parameter an explicit type and size:

command.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 20).Value = orderNumber;

Change the column when it really holds Unicode text, such as names in several scripts. Make it NVARCHAR and keep the parameters NVARCHAR. Changing a column’s type on a large table is a migration, so plan it.

Don’t fix it by converting the parameter inside the query. WHERE OrderNumber = CAST(@Number AS VARCHAR(20)) works, but now every query against that column has to remember it. Fix the declaration instead.

Checklist

  • A seek you expected is a scan, and the root operator has a warning triangle: check for CONVERT_IMPLICIT around the column.
  • VARCHAR column, NVARCHAR value: the column is converted. Change the value’s type.
  • String column, number value: every row is converted, and non-numeric rows raise error 245.
  • Number column, string value: the string is converted once. Harmless, but tidy it up.
  • Check your data access layer: AddWithValue, Dapper strings, and EF Core models without IsUnicode(false).

SpDev Pro looks for this in the actual plan of every statement it profiles. When it finds a converted column, it puts the finding on the line, names the column and the type the parameter should have, and shows how many rows the scan read to keep the few it returned. The homepage example is exactly this bug.