Difference between Implicit Conversion and Explicit Conversion in SQL Server

Question

Could you kindly explain the differences between explicit and implicit conversion in SQL Server?

Answer 1

When you explicitly use the CONVERT or CAST keywords in your query, a conversion takes place.

When different datatypes are present in an expression and the SQL Server automatically casts them in accordance with the principles of datatype precedence, an implicit conversion occurs.

As an illustration, nvarchar has a higher priority than varchar.

CREATE TABLE Demo
(
X varchar(50) PRIMARY KEY
)

/*Explicit*/
SELECT *
FROM Demo 
WHERE CAST(X AS NVARCHAR(50)) = N'Foo'

/*Implicit*/
SELECT *
FROM Demo 
WHERE X = N'Foo' /*<-- The N prefix means nvarchar*/

The second execution plan shows a predicate of:

CONVERT_IMPLICIT(nvarchar(50),[D].[dbo].[Demo].[X],0)=[@1]

Both the explicit and implicit conversions prevent an index seek in this case.