There are many data types that can be used in SQL Server. Picking the proper data type is essential, however it is also important to know when SQL Server will automatically complete a type conversion. When SQL Server automatically converts data types, it will use the Data Type Precedence to determine what will be the target data type for the conversion.
When does SQL Server need to automatically convert data types? There are a number of places, however you will commonly see this when joining on columns that are not the same data type or in the WHERE clause when comparing two values that are not the same data type.
This image below is a screenshot of the list that was taken from the following Microsoft document, Click Here.
Let’s take a few minutes to go over where data types are used in SQL Server and how to gather information about the data types. As expected you can use them when creating tables and defining columns. Each column will have a data type assigned to it. If you look at the image below you can see the various data types on the Production.Product table in the AdventureWorks2014 sample database.
Another way to get the data types for a particular table is to run one of the following queries. Of course you will need to change the parameter at the end to the name of the table you are looking for. Below are two examples. One using an information_schema view, while the other is using the sys.columns table.
SELECT TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, ORDINAL_POSITION
, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘salesorderheader’
SELECT name
, Type_name(user_type_id) AS ‘DataType’
, max_length
, precision
, scale, is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(‘production.product’)
You will also see data types used with declaring variables and stored procedure parameters. We declaring a variable, can you not only provide a data type but can also assign the variable a value on the same line.
DECLARE @amount INT = 20
Just an FYI, you can still use the SET keyword to assign the variable a value, as in the code below.
DECLARE @amount INT
SET @amount = 20
Now that the variable is declared and assigned a value, it can now be used. The type conversions will happen in a few places in the TSQL. Most commonly it will be seen in the WHERE clause when the datatype in the column is different than the data type of the variable. Using the query below as an example, the variable has been incorrectly declared as a Varchar data type, while the SalesOrderID column is an INT database. This difference is what is forcing a type conversion when doing the comparison.
DECLARE @SorderID VARCHAR(20)
SET @SorderID = ‘43659’
SELECT *
FROM [Sales].[SalesOrderHeader]
WHERE [SalesOrderID] = @SorderID
How Do I know an Auto Type Conversion took place? Well there are a couple of different methods. The first method is to use an Execution Plan. Before running your query, click the button in the red box below.
When looking at an Execution plan you will see a warning sign on the operator if there is an issue. You can see this below on the operator to the far left, it is a yellow triangle. This can be a warning for a few things, implicit convert is just one of them.
To find out if this is indeed an implicit convert warning float your mouse over it. When you do, you will see a popup similar to below. This clearly shows that a convert took place.
This not only tells you that the convert happened, it also identifies the consequence of the convert. In this case, if the Cardinality Estimate is affected in a negative manner, the query optimizer may pick a less than ideal plan. This could lead to a performance problem.
Another approach you can use to find out if an implicit convert happened is to use the sql_variant_property function. This function accepts two arguments, an expression and a property. The expression is what you think it might be, this is what we would like to evaluate for the datatype. While the property has multiple options, we are going to focus solely on using the BaseType property. We will use this to determine the data type of an expression. Click here for find more information about the sql_variant_property.
If you look at the code below, you will see that there are two variables being declared and then in turn being passed into the first argument of the sql_variant_property function. Notice that the two variables are different types, one TinyInt while the other is varchar.
DECLARE @one TINYINT
DECLARE @two VARCHAR(20)
SET @one = 1
SET @two = ‘2’
SELECT SQL_VARIANT_PROPERTY(@one + @two,‘basetype’) AS ‘ResultOfExpression’
, SQL_VARIANT_PROPERTY(@one,’basetype’) AS ‘DataTypeOf @one’
, SQL_VARIANT_PROPERTY(@two,’basetype’) AS ‘DataTypeOf @two’
When you run the above query you will get the results below. Notice that the second two column return the same data type as the one specified when each of the variables were declared. As for the first column, I am taking a TINYINT data type and adding it to a varchar datatype. Because the data types are different, SQL Server will automatically convert the result to the variable data types that are higher up the precedent level. In this case the resulting value will be a TINYINT. SQL Server converted the ‘2’ to an tinyint data type from a varchar data type.
One additional tool you can use to capture implicit converts is Extended Events. If are not familiar with Extended Events this is just a bit outside the level of this post. However, I will cover a few basics. Extended Events is a tool that can be used to capture information about what is happening on your server. Here is a link of a session I did at the SQL Saturday event in Dallas in 2018. This will give you a high level overview of how to create a session. Click Here. Also, Grant Fritchey has a number of great blog posts on Extended Events. They can be found here.
If you are familiar with Extended Events, the event you will want to use is the plan_affecting_convert event.
Here is the description of this event:
“Occurs when a type convert issue affects the plan. The expression value indicates the conversion that can cause inaccurate Cardinality Estimation or prevent from using a seek based plan in the query plan choice. If performance is affected, rewriting the query could help.”
Below is an example of what the data will look like. We are able to collect the time as well as the query. While these additional items are not in the below image, you can also collect the host, database name and login name.
Now that we have covered what implicit converts are, you might be wondering how can I prevent the auto conversions? This is more of an matter of design than anything else. Below are a few suggestions that will help prevent type conversions.
-
-
- Declare data types appropriately for variables when writing TSQL code
- User proper data types when designing parameters for stored procedures
- Chose proper data types for table columns
-
In summary, there are a number of tools that we can use to identify when implicit converts. Once you know when they are happening you can now begin to assess if it is causing a performance issue or not. If so, you can begin to work on a solution.
Thanks for visiting my blog!!!