Difference between Implicit Conversion and Explicit Conversion in SQL Server
implicit-conversion, sql, sql-server
Solution
An explicit conversion occurs when you use the `CONVERT` or `CAST` keywords explicitly in your query.
An implicit conversion arises when you have differing datatypes in an expression and SQL Server casts them automatically according to the rules of datatype precedence.
For example nvarchar has higher precedence 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.
Problem
What is the difference between implicit conversion and explicit conversion in SQL Server?