Implicit conversion occurs when SQL Server needs to automatically convert data from one data type to another. It typically happens in scenarios where you move, compare, or combine data. For those who would like to learn more about this topic, I recommend the Microsoft learning resources.

In this blog post, we will discuss some problems that may occur during the implicit conversion and how to address them. Since the implicit conversion is automatic, it is crucial that you as a developer are conscious how data types you define affect general performance of your application or infrastructure that the SQL Server is part of.

Here’s just a couple of examples of when the SQL Server would perform an implicit conversion of data types:

  • nvarchar to varchar
  • numeric to int

You’ll find a complete conversion table that will show you which data types are implicitly converted at the end of the post.

Common problem with data types

Implicit conversion gets more problematic when you’re handling big data. It can drastically affect performance. Let’s take a closer look at the example.

A table with 1 million records

We’ll use an input parameter with a type of nvarchar and a table column with a type of varchar. This situation triggers the implicit conversion and that consumes CPU and doesn’t use index.

The table dbo.Workers have 1 000 000 records and two columns:

  • Id INT
  • Name VARCHAR(255)

The column Name is indexed.

SQL Server table dbo.Workers

Wrong data type usage

Here’s an example of what happens when we make a call with the WRONG data type, NVARCHAR(255)

SQL Server table with nvarchar

For the returned zero records, the SQL Server did an index scan, which means the server iterated over all index items and read the entire table. Remember that it had 1 million records? Now, it’s when the size matters because with such a massive table it took a whooping 125 ms of CPU, which is not ideal.

Correct data type usage

Let’s now take a look at the next example. Let’s see what happens when we make a call with the CORRECT data type, VARCHAR(255).

SQL Server with varchar

For the returned zero records, the SQL Server did an index seek, which means the server iterates over selected index items and it only reads selective rows from the table. Thanks to such precision, performance was totally unaffected. It took precisely 0 ms of CPU.

Conclusion

From the examples mentioned earlier, we can clearly see that the wrong query usage can eat up the CPU. You must be careful with data types and consider how others interact and use data.

If you have a lot of calls of a query with implicit conversion, you can easily KILL a complete SQL server performance.

Conversion table

Data type precedence can be found in the Microsoft learning resources.

conversion table