Understanding Implicit Type Conversion in SQL Server-CodePudding

On the SQL Server documentation page, MS provides the following matrix showing what conversions are supported and not:

What would be an example in SQL of an explicit conversion and an implicit conversion?

For example, I would assume that an explicit conversion would be something like CAST('2014-01-01' AS DATE), but then it also allows odd things like converting varchar to image. Or, how could you explicitly cast a datetime to a float?

In the query below, we have requested NationalIDNumber equal to the integer value 14417807. For SQL Server to compare these two data types, it must convert that NVARCHAR into INT. Which means every value in that column must go through a conversion process which causes a table scan.

USE AdventureWorks2016CTP3   
GO   
SET STATISTICS IO ON   
GO   
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle  
FROM HumanResources.Employee  
WHERE NationalIDNumber = 14417807

In the execution plan, you will see an exclamation point warning you that there is a potential issue with the query. Hovering over the SELECT operator, you will see that a CONVERT_IMPLICIT is happening which may have affected the optimizer from using a SEEK.

(1 row affected) Table 'Employee'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now the question is how do we fix it. It’s really simple but it does require a code change. Let’s look back at our query.

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle  
FROM HumanResources.Employee  
WHERE NationalIDNumber = 14417807

Remember we asked for an integer value. Just by adding single quotes to the value, we can eliminate our issue. It’s important to always know what data types your columns are when querying them. In this case, since it is an NVARCHAR, all I need to do is to supply a character value. This is accomplished by adding single quotes around the value.

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle  
FROM HumanResources.Employee  
WHERE NationalIDNumber = '14417807'

It’s simple to see the results. Note above the Scan count 1, logical reads 9, physical reads 0. When we rerun it we get the below.

(1 row affected)

Table 'Employee'. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can also see in the graphical plan that the warning is now gone, and we have a SEEK instead of the SCAN which is much more efficient.

Source