Implicit Conversion Performance Impact
in SQL Server
This post can help you to understand Implicit Conversion Performance Impact in SQL Server. First let’s understand the implicit conversion and then we’ll see how it impacts the performance.
Q. Why Conversion Required?
Ans:
Data conversion has to be occurred whenever we need to compare data with two different datatypes. This comparison happens based on the data type precedence, lower precedence data types will always be implicitly converted up to the higher precedence type.
Q. What are the types of conversions?
Ans:
There are two types of data conversions:
Explicit Conversion: When you explicitly convert data using data conversion functions CAST or CONVERT is known as explicit conversion. This conversion is clearly visible to the user.
Ex: WHERE Col1 = CAST($698.4 AS VARCHAR(10));
Implicit Conversion: SQL Server internally converts data from one data type to another. This conversion can’t be visible to the user. For example an INT type column is compared with TINYINT type column then SQL Server internally converts TINYINT to INT as INT have the higher precedence than TINYINT.
— Col1 – TINYINT and Col2 INT
EX: WHERE Col1 = Col2
