sql隐式转换_SQL Server中的隐式转换

sql隐式转换

This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of implicit conversion

本文将概述SQL Server隐式转换，包括数据类型优先级和转换表，隐式转换示例以及检测隐式转换发生的方法

SQL Server query performance issues are one of the most challenging processes for database administrators. In some cases, query performance can cause huge problems and it might affect the whole SQL Server instance performance. That is why every database administrator has some knowledge about query performance and troubleshooting methodologies.

SQL Server查询性能问题是数据库管理员面临的最具挑战性的过程之一。 在某些情况下，查询性能可能会导致巨大的问题，并且可能会影响整个SQL Server实例的性能。 这就是为什么每个数据库管理员都具有有关查询性能和故障排除方法的一些知识的原因。

Certainly, there are lots of factors and reasons which affect the query performance. However, the main principle or technique to solve these types of problems is based on well understanding and interpreting of the SQL execution plan of query. The SQL Execution plans show us series of steps which are taken during query execution, so we can uncover and find out any performance problems related to query. After all this brief information about query performance issues, we will discuss and learn the details of this kind of topic which affects query performance which is named as implicit conversion.

当然，有很多因素和原因会影响查询性能。 但是，解决这类问题的主要原理或技术是基于对查询SQL执行计划的充分理解和解释。 SQL执行计划向我们展示了在查询执行过程中采取的一系列步骤，因此我们可以发现并发现与查询相关的任何性能问题。 在获得了有关查询性能问题的所有简短信息之后，我们将讨论并学习这种影响查询性能的主题的详细信息，该主题称为隐式转换。

In some cases, we can combine two different data types in a join condition or we can compare two different data types in the where clause. In these cases, SQL Server tries to convert one data type to another during the query execution process. This data conversion process is referred to as Implicit Conversion because this type of conversion is made in behind of scenes by the SQL Server Query Optimizer and, as such, the process is abstracted from users. However, we can notice this type of conversion in the execution plan of the query.

在某些情况下，我们可以在联接条件中组合两种不同的数据类型，也可以在where子句中比较两种不同的数据类型。 在这些情况下，SQL Server会在查询执行过程中尝试将一种数据类型转换为另一种数据类型。 此数据转换过程称为“隐式转换”，因为这种类型的转换是由SQL Server查询优化器在后台进行的，因此，该过程是从用户中抽象出来的。 但是，我们可以在查询的执行计划中注意到这种类型的转换。

数据类型优先 (Data type precedence)

Before we start discussing implicit conversion, we will review the concept of data type precedence in SQL Server. As we have already noted, the miss-matched data types have to be converted to compatible formats by SQL Server and this data type conversion is also done according to a defined process governed by precedence. The data type precedence rule specifies which data type is converted to the other. The data types have precedence order for SQL Server and the lower precedence data type is converted to the higher precedence data type.

在开始讨论隐式转换之前，我们将回顾SQL Server中数据类型优先级的概念。 正如我们已经提到的，必须由SQL Server将未匹配的数据类型转换为兼容格式，并且此数据类型转换也要根据由优先级控制的已定义过程进行。 数据类型优先规则指定将哪种数据类型转换为另一种数据类型。 数据类型具有SQL Server的优先级顺序，较低优先级的数据类型将转换为较高优先级的数据类型。

1. highest) 最高 ）
2. sql_variant
   sql_variant
3. xml
   XML文件
4. datetimeoffset
   日期时间偏