Database Reference
In-Depth Information
Figure 19-1.
Cost of a query with and without implicit data type conversion
The complexity of the implicit data type conversion depends on the precedence of the data types involved in the
comparison. The data type precedence rules of SQL Server specify which data type is converted to the other. Usually,
the data type of lower precedence is converted to the data type of higher precedence. For example, the
TINYINT
data
type has a lower precedence than the
INT
data type. For a complete list of data type precedence in SQL Server 2014,
please refer to the MSDN article “Data Type Precedence” (
http://bit.ly/1cN7AYc
)
. For further information about
which data type can implicitly convert to which data type, refer to the MSDN article “Data Type Conversion”
Note the warning icon on the
SELECT
operator. It's letting you know that there's something questionable in this
query. In this case, it's the fact that there is a data type conversion operation. The optimizer lets you know that this
might negatively affect its ability to find and use an index to assist the performance of the query. This can also be a
false positive. If there are conversions on columns that are not used in any of the predicates, it really doesn't matter at
all that an implicit, or even an explicit, conversion has occurred.
When SQL Server compares a column value with a certain data type and a variable (or constant) with a different
data type, the data type of the variable (or constant) is always converted to the data type of the column. This is done
because the column value is accessed based on the implicit conversion value of the variable (or constant). Therefore,
in such cases, the implicit conversion is always applied on the variable (or constant).
As you can see, implicit data type conversion adds overhead to the query performance both in terms of a poor
execution plan and in added CPU cost to make the conversions. Therefore, to improve performance, always use the
same data type for both expressions.
