SQL Server Tutorial: Implicit conversion

---

Now that you have an idea what are the most common data types in SQL Server, let's discuss how to convert data from one type to another.

There is an important rule you need to keep in mind: for comparing two values, they need to have the same data type.

If they are different, there are two options:

SQL will try to convert one type to the other behind the scenes. This is called implicit conversion.

If that's not possible, you need to explicitly convert them, using the functions CAST() or CONVERT(). Let's start with some examples and figure out what conversions can be done automatically.

Say you have this simple query from the "ratings" table. The "cocoa_percent" is a decimal column. You want to restrict the results, adding a WHERE clause to compare the percentage to several values.

This code obviously works and will return a list with all the percentages greater than 0.5.

Comparing a decimal to an integer is also possible.

What do you think will happen when executing this query?

It will work and the "cocoa_percentage" will be implicitly converted from decimal to datetime.

The value 0 corresponds to the date "01-01-1900". Adding 1 to the decimal number will add one day when it's converted to a datetime. Converting the number 365 to datetime will produce the date "01-01-1901".

This query, however, will generate an error, because the server cannot understand what we want to achieve by comparing the letter "A" to a decimal column.

And last but not least, let's do the comparison with the character "0.5".

Surprisingly, this query will work. The difference between this and the previous query, is that the character "0.5" can be interpreted by SQL Server as a number and is implicitly converted.

For every comparison present in a WHERE clause, the data type of the two operands is evaluated. If they differ, SQL Server will check if one can be automatically converted to another, without data loss.

This means, for example, that we cannot implicitly convert a decimal number to an integer, because we would lose the information after the decimal point. For this, a precedence list has been created with the order of the data types.

You see in this table a summary with the possibilities of implicit conversion between several types. On rows, there are the types from which you can convert to one of the types from the columns. For example, the cell in the 4th row and the first column describes converting an integer to a datetime. The green value inside the cell means that the implicit conversion is possible.

Microsoft has created a detailed picture with all data types and the possibility to convert them from one to another. You can look it up on MSDN and you should keep it in mind when working with data.

Even if implicit conversion is a nice feature, you should be careful because it has an impact on query performance.

You need to keep in mind that SQL Server performs the conversion for every single row of your query.

Implicit conversions are easy to prevent when you have a good database schema design and developers follow good application coding techniques.

Now it's time to do some conversions by yourself and see how they work.

#DataCamp #SQLServerTutorial #FunctionsforManipulatingDatainSQLServer #ManipulatingDatainSQLServer