Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).
There are two types of conversions – implicit and explicit.
Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.
Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.
SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:
Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98' Set @uniqueidentifier = @varchar Select @uniqueidentifier
Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.
The word of caution
Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.