On the SQLServer documentation page, they provide 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 explcitly cast a datetime to a float?
>Solution :
We have a table Employee with a NationalIDNumber column defined with a NVARCHAR data type. In this query, we will use a WHERE clause to search for a specific ID.
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.
