When a VARCHAR Isn't a VARCHAR

You’ve got an index on a column which the query filters on, but the execution plan shows a scan. This is one of the more frustrating things to debug, especially when the query looks perfectly fine and there’s no error to point at.

One common cause is an implicit conversion. SQL Server is converting something behind the scenes to make the types match, and that conversion means it can’t use the index the way you’d expect.

What’s happening

Say you have a table with a CustomerCode column defined as VARCHAR(10), with an index on it. A query comes in filtering on that column with an NVARCHAR parameter, which is what you get from a lot of application-generated queries or when a variable is declared as NVARCHAR.

SQL Server needs to compare VARCHAR data to an NVARCHAR value. Rather than converting the single parameter value, it converts every value in the column. That means it has to read every row to do the comparison. The index is still used, but as a scan, not a seek.

No error or warning is shown in the query output, but the plan looks wrong and the query runs slowly.

Finding them

The execution plan will show a warning triangle on the affected operator, but it’s easy to miss if you’re not looking for it. The more reliable approach is querying the DMVs:

-- Find queries with implicit conversion warnings
-- These are candidates for type mismatch investigation
SELECT TOP 50
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%PlanAffectingConvert%'
ORDER BY avg_logical_reads DESC;SQL

The PlanAffectingConvert string in the XML plan is what SQL Server writes when a conversion has changed the plan. This won’t catch every implicit conversion, but it catches the ones that are actually hurting performance. One caveat: casting the query plan to NVARCHAR(MAX) and running a LIKE over it is expensive on a busy instance. Run it during a quiet period or against a specific time window using qs.last_execution_time to narrow the results.

A simpler diagnostic

If you’ve got a specific query you’re looking at, run it with SET STATISTICS IO ON and look at the logical reads. Then check the actual execution plan in SSMS. If there’s a yellow warning triangle on a seek or scan operator, hover over it. It’ll tell you exactly which columns are involved and what the conversion is.

Fixing it

The fix is usually straightforward once you’ve found the mismatch: make the parameter or variable type match the column type. If the column is VARCHAR, declare the variable as VARCHAR. If it’s NVARCHAR, make the column NVARCHAR.

-- If CustomerCode is VARCHAR(10), this causes an implicit conversion:
DECLARE @Code NVARCHAR(10) = N'CUST001';
SELECT * FROM Customers WHERE CustomerCode = @Code;

-- This doesn't:
DECLARE @Code VARCHAR(10) = 'CUST001';
SELECT * FROM Customers WHERE CustomerCode = @Code;SQL

The N prefix on a string literal also matters. N'value' is NVARCHAR. 'value' is VARCHAR. If you’re filtering a VARCHAR column with N'...' literals in ad-hoc SQL, that’s an implicit conversion.

Why it comes up so often

A lot of it comes from application code that defaults to NVARCHAR for everything. That’s reasonable from an application perspective, since NVARCHAR handles Unicode and VARCHAR doesn’t. But if the database schema was designed with VARCHAR columns and the application is passing NVARCHAR parameters, you’ve got a mismatch at every query.

The answer there isn’t necessarily to change all the application parameters. It might be to change the column types to NVARCHAR if Unicode support is needed, or to add explicit casts in the right places, but it’s not something you should ignore.

Worth a look in any database that’s had performance complaints but no obvious cause.