Fix implicit conversions

The problem with implicit conversions

Implicit conversions happen when SQL Server has to convert one data type into another in order to perform an operation.

For example, if you compare a VARCHAR column with an NVARCHAR value, SQL Server has to convert the column value to NVARCHAR before it can make the comparison.

That can turn a fast index seek into a full table scan.

Here is a query that causes the problem:

DECLARE @Name NVARCHAR(50) = 'Feragotto';

SELECT *
FROM Contact.Contact
WHERE Name = @Name

If Name is a VARCHAR column in the database while the @Name parameter is NVARCHAR — which is the ADO.NET default — SQL Server has to convert the Name column to NVARCHAR before comparing. This can prevent the index on Name from being used, and prevent a correct estimate of the number of rows returned, that is, correct cardinality estimation.

Why the column gets converted rather than the parameter

When SQL Server has to compare two values of different data types, it implicitly converts one of them so the types are compatible. The general rule is that SQL Server converts towards the data type with the higher precedence. So when a column is VARCHAR and a parameter is NVARCHAR, SQL Server converts the column to NVARCHAR rather than converting the parameter to VARCHAR.

These precedence rules are common to every programming language that allows implicit conversions in comparisons, for two reasons:

1. Minimising data loss. Converting a VARCHAR value to NVARCHAR is far less likely to lose data than the other way round. Converting NVARCHAR to VARCHAR can drop characters that have no representation in VARCHAR.

2. Consistency and predictability. Following a clear precedence hierarchy means data type conversions happen the same way every time.

ADO.NET and NVARCHAR

ADO.NET uses NVARCHAR as the default data type for the parameters of prepared queries. That is a problem when the database columns are VARCHAR: SQL Server has to convert the column data to NVARCHAR before it can compare, which costs performance.

So if you have a table with a VARCHAR column named Name and you use ADO.NET to run a query with an NVARCHAR parameter, SQL Server converts the Name column to NVARCHAR before comparing.

Identifying implicit conversions

To spot implicit conversions in your queries, use SQL Server execution plans. They show how SQL Server runs a query, and they report implicit conversions.

In this plan you can see a warning icon on the SELECT operator that summarises the statement. That warning means an implicit conversion took place. Hovering over the icon shows the details, including the data types involved.

The warning can report two distinct problems: a performance problem, because an index seek is no longer possible, and a cardinality estimation problem. The cardinality estimation problem is sometimes harmless — an implicit conversion in the SELECT list, for instance — but the seek warning relates to an implicit conversion in a predicate, and that is a sign the query needs fixing.

That is the case in the example above, where the implicit conversion prevents the index on the Name column from being used.

Detecting implicit conversions across the workload

You can trace these warnings with an Extended Events session. The session creation script is on my GitHub.

You can also query the plan cache to find plans containing implicit conversions.

Fixing implicit conversions

To fix an implicit conversion, change the data types in your queries so they match the data types in the database. If the Name column is VARCHAR, change your query to use a VARCHAR parameter rather than an NVARCHAR one.

Here is what the fix looks like:

// Before: the NVARCHAR default
command.Parameters.AddWithValue("@Name", name);

// After: VARCHAR, stated explicitly
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;

Entity Framework and implicit conversions

By default, Entity Framework can use data types that do not match the data types in the database exactly. If you have a VARCHAR column in your database, Entity Framework may use a .NET type that maps to NVARCHAR in SQL Server, which produces implicit conversions when the queries run.

In earlier versions of Entity Framework, strings were mapped to NVARCHAR by default. In Entity Framework Core you can specify the data type to use for each property of your model.

Here is how to specify the data type for a property:

public class Contact
{
    public int Id { get; set; }

    [Column(TypeName = "varchar(50)")]
    public string Name { get; set; }
}

In this example the Name property maps to a VARCHAR(50) column in the database, which avoids the implicit conversion when queries run.

You can also configure data types in the entity configuration class. With the Fluent API, specify the data type like this:

modelBuilder.Entity<Contact>()
    .Property(c => c.Name)
    .HasColumnType("varchar(50)");

This makes sure the Name column maps to a VARCHAR type in the database.

Here is a complete entity configuration with the Fluent API, written to avoid implicit conversions:

public class ContactConfiguration : EntityTypeConfiguration<Contact>
{
    public ContactConfiguration()
    {
        Property(c => c.Name)
            .HasColumnType("varchar(50)")
            .IsRequired();
    }
}