Implicit and Explicit conversion is done to convert the data from one data type to another data type
Implicit conversion is the conversion which is done by SQL server automatically
Explicit conversion is the conversion done by a user to convert datatypes using cast, convert parse, etc. functions
Here we are going to compare both of the conversion types with respect to performance
Example:
I will be using these two tables one is the User table and another one is the PostType table for comparison.
Firstly I am going to execute a Query without any conversion with Set Statistics Time on to displays the number of milliseconds required to parse, compile, and execute each statement and.
Query :
Set Statistics Time on
Select usr.id, usr.Displayname,usr.Points,ptype.Type
from dbo.Users usr
Left join PostTypes ptype
on usr.Id= ptype.Id
Where usr.AccountId=’96266'
In the above example, the CPU time required for the whole query to execute is 0
That means it does not cost much and takes less time too
Implicit Conversion
Query:
Set Statistics Time on
Set Statistics io on
Select usr.id, usr.Displayname,usr.Points,ptype.Type
from dbo.Users usr
Left join PostTypes ptype
on usr.Id= ptype.Id
Where usr.AccountId=N’96266'
This takes more CPU Time then Explicit Conversion
Explicit Conversion
Query:
Set Statistics Time on
Set Statistics io on
Select usr.id, usr.Displayname,usr.Points,ptype.Type
from dbo.Users usr
Left join PostTypes ptype
on usr.Id= ptype.Id
Where usr.AccountId=Convert (Varchar,N’96266')
This takes comparingly less CPU Time then implicit Conversion which shows that Explicit Conversion is faster than Implicit conversion
If you are new to SQL Server start with the following must-watch video: -
