Data Type Conversion

Data Type Conversion

Data type conversion is when a particular data type is converted into another data type.  There are several scenarios when data type conversions happen .

  1. When you combine or compare data between tables (table1. customerid  = table2.customerid)
  2. When you move data from one place to another for example from a text file into a table in your database.
  3. Moving data to a Program Variable.

Explicit and Implicit

Data type conversion are either explicitly defined or occur implicitly without the user knowing.

Explicit conversions use CAST and CONVERT functions. You use one of these functions explicitly by typing  for example:

  1. CAST( $2025.00 AS nvarchar(12)) converts money data type into a character string data type.
  2. CONVERT(nvarchar(12), $2025.00) converts money data type to a character string data type.

Implicit conversions typically happen transparently to the user. SQL server will perform an implicit conversion when you compare or combine two columns of data with each other and the data types are not an exact match for example:

  1. WHERE <column of smallint  type> = <column of  int type> The smallint datatype would be converted to the int data type implicitly before comparison.
  2. SELECT <smallint data type> + <column of int data type>  adding a smallint and int together

Conversion Chart

data type conversion chart
Data type conversion chart courtesy of Microsoft Corp.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Remarks

Understanding the difference between an implicit and explicit conversion of a data type is important.

There are certain scenarios however where implicit conversions may be causing performance issues so it is important that you be aware that SQL Server is capable of implicit conversions.

Parting Shot

It was fun writing this brief introduction to data type conversions.  I hope you enjoyed the quick read and think about this:

~Its so dusty outside today the rabbits are digging their holes six feet in the air.

Share the Knowledge

Leave a Reply