Implicit conversion of data types in the query engine

Mixing  different but compatible data types  in a sql statement , where columns and variables are used  leads to implicit conversions. Implicit conversions can lead to decreased performance.

The SQL Server functions CAST() and CONVERT() are used for explicit conversion. The main purpose is to achieve a required result. Using these functions suggests the developer understands the performance impact.

SQL Server  also performs implicit conversions. The implicit conversion is not the intended purpose by the developer. They can easily remain unnoticed . For small data sets it may not have a large impact but for large data sets , there can be a big impact.

An implicit conversion occurs when SQL Server isn’t able to compare data from two data types.  The data types are compatible but different. The SQL Server engine must first convert one data type and then compare the values.

Here is a simple example of how an implicit conversion can creep into the query plan.  Assume “col1” is defined as a nvarchar data type. The developer decides to use a value defined with the data type INT  as a search argument

. 
SELECT * FROM dbo.MyTable WHERE col1 = 737271 

When you investigate the execution plan , you’ll notice a CONVERT_IMPLICIT on the execution plan details.

One of the potential impacts of a CONVERT_IMPLICIT is the use of an index scan versus the index seek. This occurs because the column values must first be converted before they can be compared.  An index seek is a more efficient mehod to access the data in comparison to an index scan.You can check for index scans , by either reading the execution plan or checking for the SQL Server profiler Scan Event

The obvious fix is to ensure the matching data types are used. Analyse how your application defines and sends parameters to the sql query.

Read More on data types and SQL performance

Find column data types using sys.types and sys.syscolumns - SQL ...

SQL Server – large value types out of row and performance - SQL ...