SQL Authority News – Download SQL Server Data Type Conversion Chart

Datatypes are very important concepts of SQL Server and there are quite often need to convert them from one datatypes to another datatype. I have seen that deveoper often get confused when they have to convert the datatype. There are two important concept when it is about datatype conversion.

Implicit Conversion: Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit Conversions: Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.

What it means is that if you are trying to convert value from datetime2 to time or from tinyint to int, SQL Server will automatically convert (implicit conversation) for you. However, if you are attempting to convert timestamp to smalldatetime or datetime to int you will need to explicitely convert them using either CAST or CONVERT function as well appropriate parameters.

Let us see a quick example of Implict Conversion and Explict Conversion.

Implicit Conversion:

Explicit Conversion:

You can see from above example that how we need both of the types of conversion in different situation. There are so many different datatypes and it is humanly impossible to know which datatype require implicit and which require explicit conversion. Additionally there are cases when the conversion is not possible as well.

Microsoft have published a chart where the grid displays various conversion possibilities as well a quick guide.

Download SQL Server Data Type Conversion Chart

Reference: Pinal Dave (http://blog.sqlauthority.com)

6 thoughts on “SQL Authority News – Download SQL Server Data Type Conversion Chart

  1. A side effect of data type conversion (implicit or explicit) is that it can have negative impact on performance. Assume you get an implicit conversion of the modifieddate in the example above, that will cause an index scan instead of an index seek (if there is an index on modifieddate column).

    If you have different datatypes in joins between two tables, you’ll suffer a lot if the implicit conversion will occur on the largest table.

    Personally I would prefer if you could turn of implicit conversion (at server, database, connection or statement level) to avoid any unwanted conversions. If you need conversion, you should always have to specify it and it would force you to think of the side effect.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s